SQL Table Joins (Left, Right, Inner, Outer)

A quick explanation of a join

I am always being asked about SQL joins, so I thought I'd write a quick blog.

SQL Joins are used to combine data from two tables, based on a common field. The returned results (called a result-set) can be used to in the same way as the result-set from a single query.

There are four different SQL Joins you can use:

• Inner: Returns the rows where the fields match in BOTH tables
• Left:  Returns all the rows from the left table and the matched rows from the right table
• Right: Returns all the rows from the right table and the matched rows from the left table
• Outer: Returns all rows from both tables, this is also called a Full Join

Some Examples

I'm going to use the following tables to demonstrate the differences. Note that the last row of each table does not have a matching row in the other table.

Employee Location EmpID EmpName 13 Jason 8 Alex 3 Ram 17 Babu 25 Johnson EmpID EmpLoc 13 San Jose 8 Los Angeles 3 Pune, India 17 Chennai, India 39 Bangalore, India

The following query

SELECT * 

FROM

    Employee Inner Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India

As you can see, the only rows returned are where there is a match in both tables. Changing the query to use the Outer Join returns a different results-set.

SELECT * 
FROM
    Employee Full Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL
NULL	NULL	39	Bangalore, India

This returns two extra rows in the results-set. These have Null values where no match has been found in the other table.

Left Joins and Right Joins are similar to Outer Joins, except that all rows are returned from only one table (the Left or Right determines which one)

SELECT * 
FROM
    Employee Left Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL

SELECT * 
FROM
    Employee Right Join Location ON Employee.empID = Location.empID

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
NULL	NULL	39	Bangalore, India

The two results-sets above show all of the rows from one table and the matching rows from the other table. Null values are used where no match has been found.

So what determines the left and right table? It is simply the position in regards to the Join. This means you could rewrite the Left Join about like so

SELECT * 
FROM
    Location Right Join Employee ON Location.empID = Employee.empID

and you would get the same results-set

Employee.EmpID	Employee.EmpName	Location.EmpID	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL