Joins in SQL

   

SQL - Joins

JOIN is one of the heavily used concept across SQL and a question on Joins is almost compulsory in any interview, this blog aims to equip you basic knowledge of join to help you in interview questions.

JOIN is used to combine records from multiple tables.

Lets take this example: 

Lets say we have two tables Users which has user specific data like name, address etc and Orders has order related details like quantity ,bill  etc.

We separate the data and store them in separate tables, to satisfy the normal forms we discussed in previous blog

Customers table:

Customer ID Customer_Name City Country

Orders Table :

Order ID Customer ID Quantity Order Date

Now, if i want to print, CustomerID, Customer_name, city and OrderIDs placed by him how would you do it? The answer is using JOIN.

a JOIN clause is used to return a table that merges the contents of two or more other tables together. For example, if we had two tables — one containing information on Customers and another containing information on the Orders of students — we could use a JOIN clause to bring them together to create a new table: a complete list of Customers and their Orders.

Now, there are many types of joins,

  • (INNER) JOIN: Returns records that have matching values in both tables 
  • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table 
  • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table 
  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Lets understand each one of them with an example:

First lets define two tables for ilustration purpose, lets fill some data in the tables we defined previously:

Customers:

Customer ID Customer Name City Country
1 Ajay Hyderabad India
2 Balaji Vizag India
3 Charan Mumbai India

Orders:

Order ID Customer ID Quantity Order Date
10308 2 7 1996-09-18
10309 2 3 1996-09-19
10310 3 8 1996-09-20

Inner Join:

Below figure represents the Inner join in Venn diagram, in true sense, Inner join is intersection of the two tables

SQL InnerJoin

Now, to perform left join we can use,

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; 

INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

Hence the o/p will be :

10308 Balaji
10309 Balaji
10310 Charan

As you can see, customer Id 1 is not included as he didnt place any order

Left Join:

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match(look at the picture below)

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers. Customer Name;

SQL LeftJoin

hence the o/p will be:

+---------------+---------+ 

| Customer_name | OrderID | 

+---------------+---------+ 

| Ajay          | NULL    | 

| Balaj         | 10308   | 

| Chara         | 10309   | 

| Chara         | 10310   | 

+---------------+---------+ 

You can see the null value for customer Ajay as he didn't place any order

Right Join:

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

hence the o/p will be:

SQL RightJoin

+---------------+---------+ 

| Customer_name | OrderID | 

+---------------+---------+ 

| Balaj         | 10308   | 

| Chara         | 10309   | 

| Chara         | 10310   | 

+---------------+---------+ 

Full Join or Full Outer Join:

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

SQL Fullouterjoint

Check the related blogs below

Basic SQL Interview Questions and Answers

SQL - Aggregate Functions Interview Questions

Read 427 times Last modified on Friday, 03 November 2017 20:22
Login to post comments

Vocabprep Banner

Please key in your e-mail address.

Be up-to-date with the happenings at Conduira!