SQL Joins Tutorial

This article describe how to get data from more than one table, using the MySQL joins.
SQL Join clause can be use when fetch data from two or more tables from the database. It’s depend on certain columns from two tables.

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• OUTER JOIN
• LEFT JOIN EXCLUDING INNER JOIN
• RIGHT JOIN EXCLUDING INNER JOIN
• OUTER JOIN EXCLUDING INNER JOIN

Let’s look into following examples for understand SQL joins.

Inner JOIN

Inner JOIN is the simplest and most understood Join. It is the most common JOIN in SQL for fetch records from 2 or more than 2 tables. This query will return all of the records in the left table (table_a) that have a matching record in the right table (table_b).
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta INNER JOIN table_b AS tb ON ta.key = tb.key

Left JOIN

Left JOIN query will return all of the records in the left table (table_a) regardless if any of those records have a match in the right table (table_b). This JOIN will also return any matching records from the right table.
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta LEFT JOIN table_b AS tb ON ta.key = tb.key

Right JOIN

Right JOIN query will return all of the records in the right table (table_b) regardless if any of those records have a match in the left table (table_a). This JOIN will also return any matching records from the left table.
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta RIGHT JOIN table_b AS tb ON ta.key = tb.key

Outer JOIN

MySQL Outer JOINs return all records matching from both tables. This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN.
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta FULL OUTER JOIN table_b AS tb ON ta.key = tb.key

Left Excluding JOIN

Left Excluding JOIN query returns all of the records from the left table (table_a) that do not match any records in the right table (table_b).
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta LEFT JOIN table_b AS tb ON ta.key = tb.key WHERE tb.key IS NULL

Right Excluding JOIN

Right Excluding JOIN query will return all of the records in the right table (table_b) that do not match any records in the left table (table_a).
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta RIGHT JOIN table_b AS tb ON ta.key = tb.key WHERE ta.key IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table_a) and all of the records in the right table (table_b) that do not match.
This Join is written as follows:

SELECT {select_list} FROM table_a AS ta FULL OUTER JOIN table_b AS tb ON ta.key = tb.key
WHERE ta.key IS NULL OR tb.key IS NULL