35  SQL Join

35.1 Querying multiple tables with Join

  • Join
  • Inner Join
  • Outer Join
    • Right outer join
    • Left outer join
    • Full outer join
  • Cross Join
  • Self Join

35.2 Join Concept

  • joins rows from more than one table according to the given criteria.
  • Generally, primary key and foreign key joined.
  • For example, Student and Contact table are joined.
  • It would be good to think of the join process as a Venn diagram.
  • But actually, it is not a set operation

35.3 Inner Join Syntax

Correct syntax for joins ANSI-SQL-92 is below syntax.

SELECT ...
FROM   Table1 JOIN Table2
ON  Table1.KolonA = Table2.KolonA;

Chinook example

Join 2 tables, Artist and Album

SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
FROM Artist A
INNER JOIN Album B
ON A.ArtistId  = B.ArtistId

Join 3 tables, Artist, Album and Track.

SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
, T.Name  AS TrackName
FROM Artist A
INNER JOIN Album B
ON A.ArtistId  = B.ArtistId 
INNER JOIN Track T ON T.AlbumId  = B.AlbumId

note ANSI-SQL-89 inner join syntax

You may see below syntax for inner join but please do not use it. Using join condition explicitly would always be better.

SELECT ...
FROM   Table1, Table2
WHERE  Table1.KolonA = Table2.KolonA;

It will still works in all databases but giving join conditions using join keyword is always better. Additionally ANSI-SQL-92 syntax for left and right joins are more understandable.

35.4 Inner Join

join inner example1- en
  • Inner join brings the records that match the join condition in both tables. In the following example, 2 tables, Artist and Album are joined and join condition is explicitly given using ON A.ArtistId = B.ArtistId line
SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
FROM Artist A
INNER JOIN Album B
ON A.ArtistId  = B.ArtistId

If column name is same in the both tables, we can forgo ON, like below. I dislike this usage and never use it. Please use explicit ON condition.

SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
FROM Artist A
INNER JOIN Album B

  • The match is made according to the columns given in the ON word. More than one column can be combined.

  • For example, if a student does not have contact information, that student’s information will not come.

  • If = operator is used in a join, it is also called an equi-join.

  • other operators like >, >= could also be used in join conditions then they are called non-equi-join. Non-equi-joins are rarely used.

See example from Perplexity

35.5 Outer Join

  • All rows from the specified table are in the result set but only the matching rows from the other table are in the result.

  • Specified table(s) is decided by using “LEFT, RIGHT, FULL” keywords.

  • All rows of the specified table are in the result set.

  • NULL is used for the column information that does not match the given join condition.

  • For example, NULL will be in the information section for new employees who do not assigned any customers information. Or, any new customer who does not have support representation employee will not be in the results.

Lets insert a new customer with no support representation with following query.

INSERT INTO Customer( FirstName, LastName,  Email )
VALUES( 'New Customer First Name', 'New Customer Last Name',  'new@customer.com');

Also insert a new employee.


INSERT INTO Employee( FirstName, LastName)
VALUES( 'New Employee First Name', 'New Employee Last Name' );

First lets start with basic Inner join

SELECT 
c.CustomerId
,c.FirstName as CustomerFirstName 
,c.LastName  as CustomerLastName
,e.FirstName as SupportRepresentatorFirstName
,e.LastName as SupportRepresentatorLastName
from Customer c INNER JOIN Employee e 
on c.SupportRepId  = e.EmployeeId

This will bring as 59 rows.

join-left-example1

Since we used inner join only those customer who has support employees are in the result. We do not see either of our newly inserted customer or employee in the result.

35.6 Right Outer Join

Change inner join to right join in our sql query.

SELECT 
c.CustomerId
,c.FirstName as CustomerFirstName 
,c.LastName  as CustomerLastName
,e.FirstName as SupportRepresentatorFirstName
,e.LastName as SupportRepresentatorLastName
from Customer c RIGHT JOIN Employee e 
on c.SupportRepId  = e.EmployeeId

We see 6 new rows in the result set. These new rows are those employee with no assigned customers. Since they have no matching information in the Customer table, the columns from Customers are null in the result.

join right Chinook example

The Venn diagram for Right join is below.

join-right-example1

35.7 Left Outer Join

Change inner join to left join in our sql query.

SELECT 
c.CustomerId
,c.FirstName as CustomerFirstName 
,c.LastName  as CustomerLastName
,e.FirstName as SupportRepresentatorFirstName
,e.LastName as SupportRepresentatorLastName
from Customer c LEFT JOIN Employee e 
on c.SupportRepId  = e.EmployeeId

We see only 1 new rows in the result set. This new row is our newly inserted customer with no support representation. Since this customer have no matching information in the Employee table, the columns from Employee are null in the result.

join left Chinook example

The Venn diagram for Left join is below.

join-left-example1

35.8 Full Outer Join

Change inner join to full join in our sql query.

SELECT 
c.CustomerId
,c.FirstName as CustomerFirstName 
,c.LastName  as CustomerLastName
,e.FirstName as SupportRepresentatorFirstName
,e.LastName as SupportRepresentatorLastName
from Customer c FULL JOIN Employee e 
on c.SupportRepId  = e.EmployeeId

We see only 7 new rows in the result set. One new row is our newly inserted customer with no support representation. And other 6 rows are those employees with no assigned customers. Any not matching information are returned as null in this result set as below.

join Full Chinook example

The Venn diagram for Full outer join is below.

join full outer example 1

35.9 SQL Self Joins

  • Joins the rows from the same table.
  • Create 2 virtual copies of the same table used in the the FROM clause. Compare the rows with each other using the join condition.
  • At least one alias is required for this operation.
  • Giving a meaningful name to aliases will help you understand sql better later.
  • In the below example, Manager (M) is used for aliases.

Chinook reports to which manager example

SELECT E.EmployeeID
      ,E.LastName AS EmployerLastName
      ,E.FirstName AS EmployerFirstName
      ,E.ReportsTo
      , M.EmployeeID as ManagerID
      , M.LastName AS ManagerLastName
      ,M.FirstName AS ManagerFirstName
  FROM Employee E 
  INNER JOIN Employee M
  ON E.ReportsTo = M.EmployeeID

35.10 Cross Join

  • Cross join Cartesian multiply the rows in two tables

  • For example, let’s make a cross join on Student and Contact tables.

Öğrenci İletişim SONUÇLAR
Öğrenci No Öğrenci Öğrenci No İletişim
1 Atilla 1 ati@email1 Atilla ati@email1
ati@email1 Atilla ati@email2
2 Aydin 2 aydin@email1 Atilla aydin@email1
Aydın ati@email1
Aydın ati@email2
Aydın aydin@email1
  • Results will 2*3 = 6 rows.
  • Apart from Test data creation, it is normally unwanted.

Following cross join sql in Chinook, cartesian multiply Genre and MediaType tables and bring us 125 rows.

SELECT  G.Name,M.Name
FROM Genre G CROSS JOIN MediaType M;
  • Genre 25 rows
  • MediaType 5 rows