Read this article to understand the Join Operations along with the examples. We have shared a few examples and we have shared basic as well as advanced level common interview questions also.
In this post, I will provide you a short overview of Join operations with all its options. In addition, we have covered some questions which we receive from the audience.
First, let’s talk about the theory of Join and why we should use it.
Definition of Join Operations
We have learned about the table and it’s data. As you know, we never store all the data into single table. We store the employee related informations into employee table while his personal details into personal table as well as insurance details into insurance tables. When we have to combine all these details into one place and generate some reports on that then we need to join those tables. For combining two or more tables we need a join keyword.
Join keyword is used to join two or more tables using certain matching criteria. While combining the records we need a matching condition so based on that will join the data and create a row with all columns from both the tables. There are five types of joins which we can use for combining the data.
Reasons to Use Join Operations
As it’s name suggest we can use the Join operations to combine the data of two or more tables. Below are the some key areas where we need to use Join.
- Create a dashboard that shows employee wise skill sets.
- Creating dashboard for showing share market trends.
- Creating reports for showing most watched web series.
- Creating reports for showing top 10 population wise countries.
Inner Join
When we have to combine the data and get only matched data based on matching condition then we use inner join. Inner join always return the matched data means we always get the record counts less than total number of rows from both table.
Syntax
SELECT table1.columns, table2.columns
FROM table1
JOIN table2 ON table1.common_column = table2.common_column
[WHERE CONDITION];Where clause is optional while common column name should be different but its values should be matched to return the matched records. Lets see below example for inner join.
Example
Example 1:
We have “Customers” table, having 5 records with few columns. We have another table “Education” having education details along with the grade. Lets print the customers along with their education and grade.
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 32 | UK |
| 4 | John | Sinha | 22 | UK |
| 5 | James | Doe | 19 | INDIA |
| e_id | cust_id | qualification | percentage | grade |
|---|---|---|---|---|
| 1 | 1 | ME | 87 | A+ |
| 2 | 2 | BE | 77 | A |
| 3 | 3 | ME | 79 | A |
| 4 | 4 | MCA | 76 | A |
| 5 | 5 | MCA | 67 | B+ |
qualification, grade FROM Customers JOIN Education ON Customers.customer_id = Education.cust_id; |
| first_name | last_name | qualification | grade |
|---|---|---|---|
| John | Doe | ME | A+ |
| Robert | Luna | BE | A |
| David | Robinson | ME | A |
| John | Sinha | MCA | A |
| James | Doe | MCA | B+ |
I hope above example clears the inner join and how to select output columns from both the tables. Sometimes we have duplicate columns in both tables and we need to select both the columns then we need to alias the column because duplicate columns not allowed in the further query. By using AS keyword we alias the columns.
Left Join
Left Join is also called as Left Outer Join. As it’s name suggest that we can combine the two or more tables using left join and returns all the rows from the left table (table1) and the matched rows from the right table (table2). If no match is found in the right table, NULL values are returned.
Syntax
SELECT table1.columns, table2.columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
[WHERE CONDITION];Where clause is optional while common column name should be different but its values should be matched to return the matched records. Lets see below example for left join.
Example
Example 2:
We have “Customers” table, having 5 records with few columns. We have another table “Education” having education details along with the grade. Lets print the customers along with their education and grade.
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 32 | UK |
| 4 | John | Sinha | 22 | UK |
| 5 | James | Doe | 19 | INDIA |
| e_id | cust_id | qualification | percentage | grade |
|---|---|---|---|---|
| 1 | 1 | ME | 87 | A+ |
| 2 | 2 | BE | 77 | A |
| 3 | 5 | MCA | 67 | B+ |
qualification, grade FROM Customers LEFT JOIN Education ON Customers.customer_id = Education.cust_id; |
| first_name | last_name | qualification | grade |
|---|---|---|---|
| John | Doe | ME | A+ |
| Robert | Luna | BE | A |
| David | Robinson | NULL | NULL |
| John | Sinha | NULL | NULL |
| James | Doe | MCA | B+ |
For customer_id 3 and 4, there is no matching row in the Education table so those values returned as NULL. Left Join will return All the records from left table and matching values from the right.
Right Join
Right Join is also called as Right Outer Join. As it’s name suggest that we can combine the two or more tables using right join and returns all the rows from the right table (table2) and the matched rows from the left table (table1). If no match is found in the left table, NULL values are returned.
Syntax
SELECT table1.columns, table2.columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column
[WHERE CONDITION];Where clause is optional while common column name should be different but its values should be matched to return the matched records.
Example
Example 2 is the example of Left join where all rows from table left are return and matching values from right table are populated. In right join will show all the records from right table and matching values from left table. Exact opposite of left join.
Full Outer Join
As it’s name suggest, it is the combination of Left outer join and Right outer Join. It will combine the two or more tables and returns all the matched rows from the left as well as right table. If no match is found in the both table, NULL values are returned.
Syntax
SELECT table1.columns, table2.columns
FROM table1
FULL OUTER JOIN table2 ON table1.common_column = table2.common_column
[WHERE CONDITION];Where clause is optional while common column name should be different but its values should be matched to return the matched records. Lets see below example for full outer join.
Example
Example 3:
We have “Employee” table, having 5 records with few columns. We have another table “Department” having department details. Lets print the Employee along with their department.
| employee_id | first_name | last_name | dept | country |
|---|---|---|---|---|
| 1 | John | Doe | 101 | USA |
| 2 | Robert | Luna | 102 | USA |
| 3 | David | Robinson | 101 | UK |
| 4 | John | Sinha | 102 | UK |
| 5 | James | Doe | 105 | INDIA |
| dept_id | dept_name | establishment |
|---|---|---|
| 101 | IT | 2010 |
| 102 | HR | 2009 |
| 103 | Finance | 2007 |
| 104 | DAI | 2013 |
dept, dept_name FROM Employee FULL OUTER JOIN Department ON Employee.dept = Department.dept_id; |
| first_name | last_name | dept | dept_name |
|---|---|---|---|
| John | Doe | 101 | IT |
| Robert | Luna | 102 | HR |
| David | Robinson | 101 | IT |
| John | Sinha | 102 | HR |
| James | Doe | 105 | NULL |
| NULL | NULL | 103 | Finance |
| NULL | NULL | 104 | DAI |
For employee_id 5, there is no matching row in the Department table so those values returned as NULL. For dept_id 103 and 104, there is no matching row in the Employee table so those values are returned as NULL.
Cross Join
Cross join is used to get the all possible combinations of both table. Cross join is a cartesian product of any two tables. Cross join doesn’t need any matching criteria because its the possibilities of all selected rows.
Syntax
SELECT table1.columns, table2.columns
FROM table1
CROSS JOIN table2
[WHERE CONDITION];Where clause is optional. Lets see below example for cross join.
Example
Example 4:
We have “Employee” table, having 5 records with few columns. We have another table “Department” having department details. Lets print the Employee along with their department using cartesian product.
| employee_id | first_name | last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Robert | Luna |
| 3 | David | Robinson |
| dept_id | dept_name | establishment |
|---|---|---|
| 101 | IT | 2010 |
| 102 | HR | 2009 |
dept_name FROM Employee CROSS JOIN Department ; |
| first_name | last_name | dept_name |
|---|---|---|
| John | Doe | IT |
| Robert | Luna | IT |
| David | Robinson | IT |
| John | Doe | HR |
| Robert | Luna | HR |
| David | Robinson | HR |
Generated cartesian product with 3 employees with 2 departments. i.e 3*2 = 6 rows. I hope all above example clears the concept of JOIN.
Linking
Join is used to combine two or more tables. We use Join inside Select query and We can use Where, Order By, Group by, Having and Limit clause. You can refer all the interview questions. Please refer all the links for understanding the Join in depth.
SQL Join operations with Latest SQL Interview Questions 2023
Basic Join SQL Interview questions
- How to join two tables in SQL?
- What are the different types of joins and how to use it?
- What is the difference between INNER JOIN and SELF JOIN?
- State the difference between the RIGHT JOIN and the LEFT JOIN.
Intermediate Join SQL Interview questions
- Can you join a table to itself in SQL?
Advanced Join SQL Interview questions
- How can you use the Group by clause with Join?
Questions covered from comment section
In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.
Que : How can you use the Group by clause with Join?
Ans : Lets understood the below SQL statement for above question. Let me know if any example is needed.
SELECT c.city, c.customer_name, SUM(o.quantity) as total_quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city, c.customer_name;Quiz
Click on this link to solve few MCQ questions on Join clause.
I hope you have Happy offline learning… Lets meet in next session.
