MySQL Joins
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
The JOIN keyword is used to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Before we continue with examples, we will list the types of JOIN, and the differences between them.
- JOIN / INNER JOIN: Return rows when there is at least one match in both tables
- LEFT OUTER JOIN: Return all rows from the left table, even if there are no matches in the right table
- RIGHT OUTER JOIN: Return all rows from the right table, even if there are no matches in the left table
- FULL JOIN: Return rows when there is a match in one of the tables
INNER JOIN
The INNER JOIN keyword return rows when there is at least one match in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
INNER JOIN is the same as JOIN.
Example:
Let us say we want to find the department manager’s name for all departments. We have the following tables.
The department info is present in the ‘departments’ table; the manager id for a department is present in the ‘dept_manager’ table whereas the ‘name’ is present in the ‘employees’ table.
So we need to join these three tables to get the information we want. We join ‘departments’ and ‘dept_manager’ on the column ‘dept_no’ (primary key in ‘departments’ and foreign key in ‘dept_manager’) and join the result with ‘employees’ table on column ‘emp_no’ (primary key in ‘employees’ and foreign key in ‘dept_manager’) as follows.
SELECT departments.dept_no, dept_manager.from_date, dept_manager.to_date, employees.first_name, employees.last_name FROM departments INNER JOIN dept_manager ON departments.dept_no = dept_manager.dept_no INNER JOIN employees ON dept_manager.emp_no = employees.emp_no ORDER BY departments.dept_no, dept_manager.from_date;
We use ‘table_name.column_name’ for referring column names to avoid ambiguity because some column names are present in more than one table.
The INNER JOIN keyword returns rows when there is at least one match in both tables. If there are rows in “employees” that do not have matches in “dept_manager”, those rows (employees who are not managers) will NOT be listed.
LEFT JOIN
The LEFT JOIN returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example:
The following query gives all the orders placed by customers. We use LEFT JOIN to get info about customers who have not placed any orders. Their order details will be shown as NULL values.
SELECT customers.customernumber, customerName,orderNumber FROM customers LEFT JOIN orders ON customers.customernumber=orders.customernumber ORDER BY ordernumber;
The LEFT JOIN keyword returns all the rows from the left table (‘customers’), even if there are no matches in the right table (‘orders’). The customer with number = 169 (Porto Imports Co.) did not place any orders, so the ‘orderNumber’ is shown as NULL thereby keeping all the rows from the left table.
RIGHT JOIN
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example:
Query to get all the employees who have helped the customers. NULL values show those employees who have not represented any customers.
SELECT customers.customernumber, customername, employees.employeenumber FROM customers RIGHT JOIN employees ON customers.salesrepemployeenumber=employees.employeenumber ORDER BY employees.employeenumber;
The RIGHT JOIN keyword returns all the rows from the right table (‘employees’), even if there are no matches in the left table (‘customers’).
Here for the employeenumber=1002, 1056, 1076, 1088, 1102, and 1143, the customer details are shown as NULL, thereby keeping all the rows from the right table (‘employees’) and filling with NULL values for left table(‘customers’).
FULL JOIN
The FULL JOIN keyword return rows when there is a match in one of the tables. MySQL does not support the FULL JOIN keyword but you can use UNION of LEFT JOIN and RIGHT JOIN to get the effect of FULL JOIN.
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL
Example:
(SELECT customers.customernumber, customername, employees.employeenumber FROM customers LEFT JOIN employees ON customers.salesrepemployeenumber=employees.employeenumber) UNION (SELECT customers.customernumber, customername, employees.employeenumber FROM customers RIGHT JOIN employees ON customers.salesrepemployeenumber=employees.employeenumber);
