Joins in SQL
Joins on Multiple Tables:
• SQL provides a convenient operation to
retrieve information from multiple tables.
• This operation is called join.
• The join operation will combine the tables into
one large table with all possible combinations
(Math: Cartesian Product), and then it will filter the
rows of this combined table to yield useful information.
Join Concepts
• Combine rows from multiple tables by specifying matching criteria
• Usually based on primary key – foreign key relationships
• For example, return rows that combine data from the Employee and SalesOrder
tables by matching the [Link] primary key to the
[Link] foreign key
• It helps to think of the tables as sets in a Venn diagram
Sales orders
that were
taken by
employees
Employee SalesOrder
Different type of Joins
1. Cross Join
2. Inner Join
i. Equi/Equality join (‘=‘)
ii. Natural join
iii. Theta join (or non-equi join (<, <=, >, >=, <>))
3. Outer Join
i. Left outer join
ii. Right outer join
iii. Full outer join
4. Self Join
5. Semi Join
Different type of Joins
Multiple Tables:
field1 field2
A 1
field1 field2
A 2
A 1
A 3
B 2
B 1
3
B 2
B 3
What kind of Join is this?
SELECT *
FROM Students S ?? Enrolled E;
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
22222 French150
[Link] [Link] [Link] [Link]
Jones 11111 11111 History105
Jones 11111 11111 DataScience194
Jones 11111 22222 French150
Smith 22222 11111 History105
Smith 22222 11111 DataScience194
Smith 22222 22222 French150
The CROSS JOIN is used to generate a paired combination of each row of the first
table with each row of the second table. This join type is also known as
cartesian join.
SELECT *
FROM Students S CROSS JOIN Enrolled E;
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
22222 French150
[Link] [Link] [Link] [Link]
Jones 11111 11111 History105
Jones 11111 11111 DataScience194
Jones 11111 22222 French150
Smith 22222 11111 History105
Smith 22222 11111 DataScience194
Smith 22222 22222 French150
Inner Joins
• Return only rows where a match is found in both input tables
• Match rows based on attributes supplied in predicate
• If join predicate operator is =, then it known as equi-join
SELECT [Link], [Link] Set returned
FROM Employee emp by inner join
[INNER] JOIN SalesOrder ord
ON [Link] = [Link]
Employee SalesOrder
SQL Inner Joins (Equi join)
SELECT [Link], [Link]
FROM Students S (INNER) JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
Note the previous version of this query (with no join keyword) is an “Implicit join”
SQL Inner Joins (Equi join)
SELECT [Link], [Link]
FROM Students S (INNER) JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Unmatched keys
Jones DataScience194
Smith French150
Joins and Inference
• Chaining relations together is the basic inference
method in relational DBs. It produces new
relations (effectively new facts) from the data:
SELECT [Link], [Link]
FROM Students S, Mortality M
WHERE [Link]=[Link]
S M
Name Race Race Mortality
Socrates Man Man Mortal
Thor God God Immortal
Barney Dinosaur Dinosaur Mortal
Blarney stone Stone Stone Non-living
Joins and Inference
• Chaining relations together is the basic inference
method in relational DBs. It produces new relations
(effectively new facts) from the data:
SELECT [Link], [Link]
FROM Students S, Mortality M
WHERE [Link]=[Link]
Name Mortality
Socrates Mortal
Thor Immortal
Barney Mortal
Blarney stone Non-living
Difference between Equi join and Natural join
SELECT * FROM student S INNER JOIN Marks M ON S.Roll_No = M.Roll_No;
1.
Natural Join joins two tables based on same attribute name and Inner Join joins two table on the basis of the column
datatypes. which is explicitly specified in the ON clause.
In Inner Join, The resulting table will contain all the
In Natural Join, The resulting table will contain all the attributes of
2. attribute of both the tables including duplicate columns
both the tables but keep only one copy of each common column
also
3.
In Natural Join, If there is no condition specifies then it returns the In Inner Join, only those records will return which exists
rows based on the common column in both the tables
SYNTAX:
SYNTAX:
SELECT *
4. SELECT *
FROM table1 INNER JOIN table2 ON
FROM table1 NATURAL JOIN table2;
table1.Column_Name = table2.Column_Name;
What kind of Join is this?
SELECT *
FROM Students S, Enrolled E
WHERE [Link] <= [Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
22222 French150
[Link] [Link] [Link] [Link]
Jones 11111 11111 History105
Jones 11111 11111 DataScience194
Jones 11111 22222 French150
Smith 22222 22222 French150
Theta Joins (<, <=, >, >=, <>)
SELECT *
FROM Students S, Enrolled E
WHERE [Link] <= [Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
22222 French150
[Link] [Link] [Link] [Link]
Jones 11111 11111 History105
Jones 11111 11111 DataScience194
Jones 11111 22222 French150
Smith 22222 22222 French150
Left Outer Join
A LEFT JOIN performs a join starting with the first (left-most) table.
Then, any matched records from the second table (right-most) will be
included. LEFT JOIN and LEFT OUTER JOIN are the same. The result is 0
records from the right side, if there is no match.
SELECT column-names
FROM table-name1 LEFT OUTER JOIN table-name2
ON column-name1 = column-name2
What kind of Join is this?
SELECT [Link], [Link]
FROM Students S ?? Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
Brown NULL
Query: List all students name and their classid irrespective whether they enrolled for
that class or not.
SELECT [Link], [Link]
FROM Students S LEFT OUTER JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
Brown NULL
Right outer join
A RIGHT JOIN performs a join starting with the second (right-most) table
and then any matching first (left-most) table records. RIGHT JOIN and
RIGHT OUTER JOIN are the same. The result is 0 records from the left side,
if there is no match.
SELECT column-names
FROM table-name1 RIGHT OUTER JOIN table-name2
ON column-name1 = column-name2
What kind of Join is this?
SELECT [Link], [Link]
FROM Students S ?? Enrolled E
ON [Link]=[Link]
[Link] [Link]
S [Link] [Link] E
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
- English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
NULL English10
Query: List all students name and their classid irrespective whether for a class any
student enrolled or not.
SELECT [Link], [Link]
FROM Students S RIGHT OUTER JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
NULL English10
SQL Joins
SELECT [Link], [Link]
FROM Students S ? JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
NULL English10
Brown NULL
Full outer join
FULL JOIN returns all matching records from both tables whether the other
table matches or not. Be aware that a FULL JOIN can potentially return very
large datasets. These two: FULL JOIN and FULL OUTER JOIN are the same.
SELECT column-names
FROM table-name1 FULL OUTER JOIN table-name2
ON column-name1 = column-name2
Query: List all students name and their classid irrespective whether a student enrolled for
a class or not, and irrespective whether for a class any student enrolled or not.
SELECT [Link], [Link]
FROM Students S FULL OUTER JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Jones DataScience194
Smith French150
NULL English10
Brown NULL
Self Join
• A self JOIN occurs when a table takes a 'selfie', that is, it JOINs with itself. A self
JOIN is a regular join but the table that it joins to is itself.
• Joining a table with itself means that each row of the table is combined with itself
and with every other row of the table. SELF JOINs are also useful for
comparisons within a table.
SELECT column-names
FROM table-name T1 JOIN table-name T2
WHERE condition
Emp-id Name Manager-id
111 Ravi NULL
112 Rakul 111
113 Pal 111
114 Yuvi 113
115 Juri 112
Query: Return all employees and the name of the employee’s manager
SQL> SELECT [Link] as Employee, [Link] as Manager
FROM Employee e1 JOIN Employee e2
ON [Link]-id = [Link]-id;
Output: Employee Manager
Ravi NULL
Rakul Ravi
Pal Ravi
Yuvi Pal
Juri Rakul
Semi-Join
Semi join is a type of join whose result-set contains only the columns
from one of the “semi-joined” tables. Each row from the first table(left
table if Left Semi Join) will be returned maximum once, if matched in
the second table. The duplicate rows from the first table will be
returned, if matched once in the second Table. A distinct row from the
first Table A will be returned no matter how many times matched in a
second Table B.
The essential differences between a semi join and a regular join when
we join two tables A and B are:
Semi join either returns each row from Table A, or it does not. No row
duplication can occur.
Regular join returns duplicates rows if there are multiple matches on the
join predicate.
Semi join returns only columns from Table A.
Regular join may return columns from either (or both) join inputs.
What kind of Join is this?
SELECT [Link], [Link]
FROM Students S ?? Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Smith French150
SQL Joins
SELECT [Link], [Link]
FROM Students S LEFT SEMI JOIN Enrolled E
ON [Link]=[Link]
S [Link] [Link] E [Link] [Link]
Jones 11111 11111 History105
Smith 22222 11111 DataScience194
Brown 33333 22222 French150
NULL English10
[Link] [Link]
Jones History105
Smith French150
END