Post
Ask Question

A Deep Dive Into Joins In SQL

As we already know, when we implement Normalization, we actually split our main table into sub-tables and remove the redundancy and inconsistency of data. Now, when we make multiple tables and when we need to extract the data from those multiple tables, obviously we need to combine them. This is the place where we need joins. This example is just for the demonstration of the concept where we need joins but it doesn’t mean that we can just implement the joins if we have applied the normalization on the database. The concept is - whenever we need to combine multiple tables, we use joins.

What is a Join?

As we can guess from the name, Join is the SQL statement with the help of which we can combine different tables.

Obviously, joins can only work when there is at least one common attribute in 2 tables. Actually, when we define the relation of 2 tables and define the foreign key in the dependent table which contains the primary key value of the parent table record, this is when both the tables are attached with each other.

Now, we have the relation between 2 tables. And now, we have various ways to combine the tables and extract the data.

  • (Inner) Join
  • Left (Outer) Join
  • Right (Outer) Join
  • Full (Outer) Join

Inner Join

It is the most frequently used join. It is also called Equi Join. Inner Join is the type of join which returns the matching values in both tables.

  1. SELECT InvoiceID  
  2.         , Customer.CustomerID  
  3.         , FirstName  
  4.         , LastName  
  5.         , Convert(nvarchar(10), InvoiceDate, 1) AS InvoiceDate  
  6.         , Total  
  7. FROM Customer  
  8. Join Invoice  
  9. ON Customer.CustomerId = Invoice.CustomerId  

Now, you might have seen the keyword INNER Join; this is also the same thing.

  1. SELECT InvoiceID  
  2.         , Customer.CustomerID  
  3.         , FirstName  
  4.         , LastName  
  5.         , Convert(nvarchar(10), InvoiceDate, 1) AS InvoiceDate  
  6.         , Total  
  7. FROM Customer  
  8. INNER Join Invoice  
  9. ON Customer.CustomerId = Invoice.CustomerId  

So, we use Inner join to get the matched value records in both tables. Now, let’s suppose we want to join 3 tables.

  1. SELECT Invoice.InvoiceID  
  2.         , Customer.CustomerID  
  3.         , FirstName  
  4.         , LastName  
  5.         , Convert(nvarchar(10), InvoiceDate, 1) AS InvoiceDate  
  6.         , Total  
  7.         , TrackId  
  8.         , UnitPrice  
  9.         , Quantity  
  10. FROM ((Customer  
  11. INNER Join Invoice ON Customer.CustomerId = Invoice.CustomerId)  
  12. INNER Join InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId)  

If you open the database schema, you’ll see we have the attributes with the same names in multiple tables. And if we want to use them in the joins where we’re combining multiple tables, then obviously we need to explicitly specify the complex attribute of which table it belongs to. And here, InvoiceId is also present in InvoiceLine and Invoice tables and both will have the same values whether we use

  • InvoiceLine.InvoiceId
  • Invoice.InvoiceId

Because it totally depends on check ON condition.

Overview Of Joins In SQL 

Correlation Table Names

It allows you to assign a temporary and shorter name of the table. Sometimes, they are called table alias.

  1. SELECT IL.InvoiceID  
  2.         , C.CustomerID  
  3.         , FirstName  
  4.         , LastName  
  5.         , Convert(nvarchar(10), InvoiceDate, 1) AS InvoiceDate  
  6.         , Total  
  7.         , TrackId  
  8.         , UnitPrice  
  9.         , Quantity  
  10. FROM ((Customer AS C  
  11. INNER Join Invoice AS I ON C.CustomerId = I.CustomerId)  
  12. INNER Join InvoiceLine AS IL ON I.InvoiceId = IL.InvoiceId)  

Look, we used AS keyword for the table alias.

Fully Qualified Object Names

In Fully Qualified Object Names, we specify the complete names.

Server.Database.Schema.Object

Actually when we’re combining the tables of multiple servers then we use this approach. It makes the better understandability and makes everything clear both to SQL engine and human as well.

  1. SELECT CustomerID  
  2.        , FirstName  
  3.        , LastName  
  4. FROM [DESKTOP-2FA7R8U].Chinook.dbo.Customer  

As you can see we are using the brackets around the server name because we are using the dash into the server name. The word dbo stands for Database Owner.

Left Outer Join

You might be confused about Left join by watching this diagram.

Overview Of Joins In SQL 

You might expect that we’ll get all the columns of table 1 and matching columns of table 2 with table 1. But it is not the case actually. With the help of left join, we’ll get all the records (rows) from the left table and all the matching records from table 2.

So don’t expect when you left join 2 tables, you’ll get all the columns from first table.

  1. SELECT c1.FirstName + ' ' + c1.LastName AS CustomerName  
  2.        , c2.FirstName + ' ' + c2.LastName AS EmployeeName  
  3. FROM Customer c1  
  4. LEFT JOIN Employee c2  
  5. ON c1.SupportRepId = c2.EmployeeId  

Right Join

It returns all the records from the right table and all the matching records from left table.

Overview Of Joins In SQL 

And the query is very similar to Left Join.

  1. SELECT c1.FirstName + ' ' + c1.LastName AS CustomerName  
  2.        , c2.FirstName + ' ' + c2.LastName AS EmployeeName  
  3. FROM Customer c1  
  4. Right JOIN Employee c2  
  5. ON c1.SupportRepId = c2.EmployeeId  

We just need to put Right instead of Left.

When you’re working with left or right joins, you’ll observe you have null values in some of your table attribute cells. Because we’re getting here 1 complete table records whether the values are matching or not.

Full Outer Join

Full Outer Join returns all the records of the tables when the values are matched on all the sides. In other words, it is basically the sum of left join and right join.

Full Join = Left Join + Right Join

Overview Of Joins In SQL 
  1. SELECT c1.FirstName + ' ' + c1.LastName AS CustomerName  
  2.        , c2.FirstName + ' ' + c2.LastName AS EmployeeName  
  3. FROM Customer c1  
  4. Full Outer JOIN Employee c2  
  5. ON c1.SupportRepId = c2.EmployeeId  

Self Join

In Self Join we join the single table with itself on the basis of some value. It is used to retrieve the records when we have some kind of relation or similarity with other records in the table. And when we’re working with self joins, we need to strictly use the alias because on the left-hand side and on the right-hand side, we have the same table. So, we use the alias to differentiate the query,

  1. SELECT e1.EmployeeId  
  2.        , e1.FirstName  
  3.        , e1.LastName  
  4.        , e1.Title  
  5.        , 'Reports To ' + e2.FirstName + ' ' + e2.LastName AS Manager  
  6. FROM Employee e1  
  7. JOIN Employee e2  
  8. ON e1.ReportsTo = e2.EmployeeId  

When we’re working with Self Joins obviously we should have the same kind of two attributes in the table. So here we have EmployeeId and ReportsTo are of the same type and containing the same kind of data inside. And now, we get this result,

Overview Of Joins In SQL 

But now, we want to show all the employees whether they can report to someone or not. So obviously we’ll use Left join to show all the records of employees.

  1. SELECT e1.EmployeeId  
  2.        , e1.FirstName  
  3.        , e1.LastName  
  4.        , e1.Title  
  5.        , 'Reports To ' + e2.FirstName + ' ' + e2.LastName AS Manager  
  6. FROM Employee e1  
  7. LEFT JOIN Employee e2  
  8. ON e1.ReportsTo = e2.EmployeeId  

And, we get this result.

Overview Of Joins In SQL 

Which Customer Lives in the Same City?

  1. SELECT c1.FirstName + ' ' + c1.LastName AS CustomerName  
  2.        , c1.City  
  3.        , c1.[State]  
  4. FROM Customer c1  
  5. JOIN Customer c2  
  6. ON c1.[State] = c2.[State]  
  7. AND c1.City = c2.City  
  8. AND c1.CustomerId <> c2.CustomerId  
  9. ORDER BY c1.[State], c1.City  

Look the logic is very simple, we’re using self-join between 2 tables and applying the condition if both cities and states are the same but the customerId would be different. Then, we can find the customers in the table who belongs to the same city.

CROSS Join

Cross Join produces the Cartesian product of the tables. The size of the Cartesian product is,

Cartesian product = total number of rows in the first table * total number of rows in the second table

  1. SELECT * FROM Employee  
  2. CROSS JOIN Invoice  

We can write the same query as,

  1. SELECT * FROM Employee, Invoice  

Now you might be wondering how it shows the result of the Cartesian product of 2 tables. If you know how to conclude the Cartesian product of 2 sets, then you have an idea of how we get the Cartesian product of 2 tables.

Overview Of Joins In SQL 

Union

Union statement is used to combine the results of two or more tables. When we’re working with the 2 sets then obviously if both sets contain any repetitive element then we only use it once in the result set. Similarly, we’re computing the Union in SQL. It just puts the repetitive element once in the resulting table.

  1. SELECT FirstName  
  2.        , LastName  
  3.        , Email  
  4. FROM Customer  
  5. UNION  
  6. SELECT FirstName  
  7.        , LastName  
  8.        , Email  
  9. FROM Employee  

Now, you might get confused between join and union. See this link.

Union All

And if we want to allow the duplicate values as well, then we’ll use UNION ALL.

  1. SELECT FirstName  
  2.        , LastName  
  3.        , Email  
  4. FROM Customer  
  5. UNION ALL  
  6. SELECT FirstName  
  7.        , LastName  
  8.        , Email  
  9. FROM Employee  

Intersect

If we want to get the same elements in both tables. Then we’ll use Intersect,

  1. SELECT FirstName  
  2.        , LastName  
  3.        , Email  
  4. FROM Customer  
  5. INTERSECT  
  6. SELECT FirstName  
  7.        , LastName  
  8.        , Email  
  9. FROM Employee  

EXCEPT

Except excludes rows that exists in both result sets. Suppose we have two tables Table A, and Table B. Table B has some values which are also present in Table A. So,

  1. Table A EXCEPT Table B = Table A (excludes matching values)  
  2. SELECT FirstName  
  3.        , LastName  
  4. FROM Customer  
  5. EXCEPT  
  6. SELECT FirstName  
  7.        , LastName  
  8. FROM Employee  

And if we want to display them with any order then we’ll use Order By. It will apply on the complete result set.

  1. SELECT FirstName  
  2.        , LastName  
  3. FROM Customer  
  4. EXCEPT  
  5. SELECT FirstName  
  6.        , LastName  
  7. FROM Employee  
  8. ORDER BY LastName  


Similar Articles