SQL Joins

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.

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 (or 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.

Syntax:

SELECT * FROM table_name1 Join_Name(INNER JOIN,LEFT JOIN,RIGHT JOIN,FULL JOIN) table_name2 ON table_name1.column_name = table_name2.column_name

Examples:

Source Tables

Left Table(Individual)

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

Right Table(Publisher)

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

1) Inner Join

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

Result

IndividualId|
FirstName|
LastName
|UserName
|IndividualId|
AccessLevel
2
Homer
Simpson
homey
2
Contributor


2) Outer Join

     a)Left Outer Join

            SELECT * FROM Individual AS Ind

     LEFT JOIN Publisher AS Pub
     ON Ind.IndividualId = Pub.IndividualId

Result


IndividualId|
FirstName|
LastName|
UserName|
IndividualId|
AccessLevel
1
Fred
Flinstone
freddo
1
Administrator
2
Homer
Simpson
homey
2
Contributor
3
Homer
Brown
notsofamous
3
Contributor
4
Ozzy
Osbourne
sabbath
4
Contributor
5
Homer
Gain
noplacelike
NULL
NULL


   b)Right Outer Join

    SELECT * FROM Individual AS Ind

    RIGHT JOIN Publisher AS Pub 
    ON Ind.IndividualId = Pub.IndividualId

Result

IndividualId|
FirstName|
LastName|
UserName|
IndividualId|
AccessLevel
1
Fred
Flinstone
freddo
1
Administrator
2
Homer
Simpson
homey
2
Contributor
3
Homer
Brown
notsofamous
3
Contributor
4
Ozzy
Osbourne
sabbath
4
Contributor
NULL
NULL
NULL
NULL
10
Administrator


3)Full Outer Join

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

Result


IndividualId|
FirstName|
LastName|
UserName|
IndividualId|
AccessLevel
1
Fred
Flinstone
freddo
1
Administrator
2
Homer
Simpson
homey
2
Contributor
3
Homer
Brown
notsofamous
3
Contributor
4
Ozzy
Osbourne
sabbath
4
Contributor
5
Homer
Gain
noplacelike
NULL
NULL
NULL
NULL
NULL
NULL
10
Administrator