Joins in MySQL

15 February 2012 By Praveen Macherla 973 views No Comment
2 Flares Twitter 0 Facebook 2 Google+ 0 2 Flares ×

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);

Tags: , , , , , , , ,