Join - A join in SQL is a clause that allows merging of records from one or more than one tables in a database.

The records from the tables are fetched based on some values that are common to each.

The SQL JOIN refers to using the JOIN keyword in a SQL statement in order to query data from two tables. When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables. You can use multiple joins in the same SQL statement to query data from as many tables as you like.

Join Types
Depending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle way

INNER JOIN: This will only return rows when there is at least one row in both tables that match the join condition. LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table. RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table. FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of the tables.

Join Syntax
Inner Join:
SELECT * FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Left Join:
SELECT * FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Right Join:
SELECT * FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Full Join:
SELECT * FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Example Inner Join Statement

SELECT * FROM Individual INNER JOIN Publisher ON Individual.IndividualId = Publisher.IndividualId

Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:
SELECT * FROM Individual AS Ind INNER JOIN Publisher AS Pub ON Ind.IndividualId = Pub.IndividualId

The next few lessons cover each type of join and show examples of usage.

SQL Inner Join

Inner Join - in this type of join, every record in the tables being joined have a matching record. The condition based on which the records are matched is called the join predicate. Implicit Vs. Explicit Inner Joins - When the clause 'Inner Join' is used, it is said to be an explicit join. See code example below: SELECT * FROM t_shooters INNER JOIN t_guntypes ON t_shooters.gun_type = t_guntypes.gun_type; Above is an explicit inner join. The same can be achieved without using the 'inner join' clause like this: SELECT * FROM t_shooter, t_guntype WHERE t_shooters.gun_type = t_guntypes.gun_type; Above is an implicit inner join.

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Equi Join - This is a type of Inner Join. (It is also called a Theta Join). - It is a join where the equality '=' operator is used. In case an operator like '<' or '>' or any other operator is used, it is not an Equi Join. An Equi Join may be used by using equality operator or the Using clause. See code example below: SELECT T_SHOOTERS.NAME, GUN_TYPE, T_GUNTYPES.DESCRIPTION FROM T_SHOOTERS INNER JOIN T_GUNTYPES USING(GUN_TYPE); Natural Join - It is a type of Inner Join. It is a join where in the join predicate is based on all the column names that are common to both the tables being joined. See code example below: SELECT * From T_SHOOTERS NATURAL JOIN T_GUNTYPES; Results will be as below: Gun_Type T_Shooters.Name T_Guntypes.Description 1 Ballu Balram Katta Pt. 5 2 Ekgoli Shikari Desi Bandook Single Barrel 3 Fauji Bhai Rocket Launcher 4 Thulla Colt 5 Jackal Colt Cross Join - Also called Cartesian Join. It is the result of joining each row of a table with each row of the other table. Outer Join - In an Outer join, each record of a table does not really need to match with a record in the corresponding table. Outer joins maybe Left Outer Joins or Right Outher Joins. Outer Joins are always explicit. Left Outer Join - This join contains all records from the left table, and matching records in the other table. However, if there are no matching records in the other table, it will still return a result, where in the records of the other table will be NULL.

Right Outer Join - This join fetches all records from the right table and only matching records from the left table. By saying left table, it means the table who's name is to the left of the Join Clause. See code example below: SELECT * FROM t_shooters LEFT OUTER JOIN t_guntypes ON t_shooters.gun_type = t_guntypes.gun_type; Full Outer Join - A full outer join merges the result fetched from Left and Right Outer joins. See code example below: SELECT * FROM t_shooters FULL OUTER JOIN t_guntypes ON t_shooters.gun_type = t_guntypes.gun_type;

_____________________________________________________________________________________ _____________ Example SQL statement

SELECT * FROM Individual INNER JOIN Publisher ON Individual.IndividualId = Publisher.IndividualId WHERE Individual.IndividualId = '2'

Source Tables

Left Table
Id 1 2 3 4 5 FirstName Fred Homer Homer Ozzy Homer LastName Flinstone Simpson Brown Ozzbourne Gain UserName freddo homey notsofamous sabbath noplacelike

Right Table

IndividualId 1 2 3 4 10 Result

AccessLevel Administrator Contributor Contributor Contributor Administrator

IndividualId 2

FirstName Homer

LastName Simpson

UserName homey 2

IndividualId

AccessLevel Contributor

SQL Outer Join

This lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.
Example SQL statement
SELECT * FROM Individual AS Ind LEFT JOIN Publisher AS Pub ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id FirstName LastName UserName

1 2 3 4 5

Fred Homer Homer Ozzy Homer

Flinstone Simpson Brown Ozzbourne Gain

freddo homey notsofamous sabbath noplacelike

Right Table
IndividualId 1 2 3 4 10 AccessLevel Administrator Contributor Contributor Contributor Administrator

Result IndividualId FirstName LastName 1 2 3 4 5 Fred Homer Homer Ozzy Homer Flinstone Simpson Brown

UserName freddo homey

IndividualId 1 2

AccessLevel Administrator Contributor Contributor Contributor NULL

notsofamous 3 4 NULL

Osbourne sabbath Gain noplacelike

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind RIGHT JOIN Publisher AS Pub ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id 1 2 3 4 5 FirstName Fred Homer Homer Ozzy Homer LastName Flinstone Simpson Brown Ozzbourne Gain UserName freddo homey notsofamous sabbath noplacelike

Right Table
IndividualId 1 2 3 4 10 AccessLevel Administrator Contributor Contributor Contributor Administrator

Result IndividualId FirstName LastName 1 2 3 Fred Homer Homer Flinstone Simpson Brown

UserName freddo homey

IndividualId 1 2

AccessLevel Administrator Contributor Contributor

notsofamous 3

4 NULL

Ozzy NULL

Osbourne sabbath NULL NULL

4 10

Contributor Administrator

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.
Example SQL statement
SELECT * FROM Individual AS Ind FULL JOIN Publisher AS Pub ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id 1 2 3 4 5 FirstName Fred Homer Homer Ozzy Homer LastName Flinstone Simpson Brown Ozzbourne Gain UserName freddo homey notsofamous sabbath noplacelike

Right Table
IndividualId 1 2 3 4 10 AccessLevel Administrator Contributor Contributor Contributor Administrator

Result IndividualId FirstName LastName 1 2 3 4 5 NULL Fred Homer Homer Ozzy Homer NULL Flinstone Simpson Brown

UserName freddo homey

IndividualId 1 2

AccessLevel Administrator Contributor Contributor Contributor NULL Administrator

notsofamous 3 4 NULL 10

Osbourne sabbath Gain NULL noplacelike NULL