Joins

Jainish Patel
Last Updated: May 13, 2022

Introduction

Consider a situation where you were assigned with a task to merge two or more tables in different ways, like combining data of table 2 with table 1 such that the final table has more dominance of table 1. We will look forward to learning JOINS in SQL to handle this type of situation. 

 

Source:me.me

JOINS is a crucial topic in SQL, and chances of getting questions from this topic are probably the highest in exams like GATE, University-level & many more.

In this article, we will be analysing how to use the JOINS statement.

Demo Database table

All the queries apply to the following Database Tables.

Consider the Students table having all the student’s data consisting of columns like RollNo, Name, City, Phone, Age.


Consider the another table Courses with details of students enrolling in the different courses consisting of columns like CourseID and RollNo.

 

JOINS

JOINS statements combine two or more tables data based on the common fields in both tables. JOINS are of many types like Cross join, Natural join, Outer join. 

In this article, we will be discussing all the joins in detail. 

Note: Table 1 is considered the Students table, and table 2 is considered the Courses table.

Let us start right away: 

Cross Join

Cross join will return data from both tables (table1 and table2). The resultant Table includes the entries of Table1 * Table2. So if Table1 has eight entries and Table2 also has eight entries, then the resultant table will contain 64 (8*8) entries.

 

Syntax

SELECT ColumnName(s) 
FROM Table1 CROSS JOIN Table2;

Let consider an example,

From the given two tables named as Students and Courses. We need to join two tables so that the resultant table includes data of both the tables and print Name and age from table 1 and CourseID from table 2.

SQL Query:  

SELECT Students.Name, Students.Age, Courses.CourseID
FROM Students CROSS JOIN Courses;

Result for the above query:

As the resultant table is too long (64 (8x8) records). So, the above image only shows some parts of the resulting table. Let us solve this dilemma by discussing an alternative. 

Natural Join

Natural Join joins two or more tables based on attribute names and values that are the same. The resulting table will have all of the table's properties and will display only common columns from the specified tables. 

 


Syntax

SELECT ColumnName(s) 
FROM Table1 NATURAL JOIN Table2;

Let consider an example,

From the given two tables named as Students and Courses. We need to join two tables so that the resultant table includes columns of both the tables with no redundancy. 

SQL Query:  

SELECT
FROM Students NATURAL JOIN Courses;

Result for the above query:


Outer Joins

In this joins, there are 4 joins: Left join, Right join, Full join. Let's look at each join one by one.

Left Join

Left join returns all the records from the left table (Table1) and the matching records from the right table (table2). The result-set will contain null, the rows with no matching row on the right side. 

Table 1 describes the left side and Table 2 describes the right side in the Venn diagram.

Syntax

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
LEFT JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

Let consider an example,

Write a query to combine the two tables so that Table1(Students) is the main table and Table2(Courses) columns are related to Table1 based on CourseID equal to RollNo, and only print RollNo and Name from Table 1 and CourseID from Table 2.

SQL Query:  

SELECT Students.Name,Students.RollNo,Courses.CourseID 
FROM Students
LEFT JOIN Courses 
ON Courses.CourseID = Students.RollNo;

Result for the above query:

Right Join

The RIGHT join function is equivalent to the LEFT join only differs to retrieve all rows from the table on the right side of the join and matches rows from the table on the left. The result-set will include null for the rows with no matching row on the left side.

Syntax

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
RIGHT JOIN Table2
ON Table1.ColumnName = Table2.Column.Name;

Let consider an example,

Write a query to join both tables such that Table2(Courses) is considered as the main table and Table1(Students) columns attached to Table2  based on RollNo of a column of Table1 equal to RollNo column of Table2 and print only RollNo and Name from Table 1 and CourseID from table 2.

SQL Query:  

SELECT Students.Name,Students.RollNo,Courses.CourseID 
FROM Students
RIGHT JOIN Courses 
ON Courses.RollNo = Students.RollNo;

The other way to achieve the same outcome: 

SQL Query:  

SELECT Students.Name,Students.RollNo,Courses.CourseID 
FROM Courses
LEFT JOIN Students 
ON Courses.RollNo = Students.RollNo;

 

Swapping the table name in the Left join query will work similarly to the Right Join. 

Result for the above query:

Full Join

The result-set of FULL JOIN is created by combining the results of both LEFT JOIN and RIGHT JOIN results. All of the rows from both tables will be included in the result-set. The result-set will contain NULL values for the rows for which there is no match.

Syntax

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
FULL JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

Let consider an example,

Students and Courses are the two tables provided. Write a query to connect the two tables so that all of the entries from both tables are included in the resultant table, with Rollno from Courses equal to RollNo from Students, and only print Name from Table 1 and CourseID from Table 2.

SQL Query:  

SELECT Students.NAME,Courses.CourseID 
FROM Students
FULL JOIN Courses 
ON Courses.RollNo = Students.RollNo;

Note: Some online compilers do not support  FULL JOIN and RIGHT  JOIN. The other way to write the query is shown below.

SQL Query:  

SELECT Students.NAME,Courses.CourseID FROM Students 
LEFT JOIN Courses 
ON Courses.RollNo = Students.RollNo
union 
SELECT Students.NAME,Courses.CourseID FROM Courses 
LEFT JOIN Students
ON Courses.RollNo = Students.RollNo;

 

FULL OUTER JOIN can be written as UNION, LEFT and RIGHT( again swapped LEFT JOIN) JOIN results.

Result for the above query:

Joins like Inner join, Self join, EQUI join and NON-EQUI join will cover in the second part of the article Joins-2.

Frequently Asked Questions

1. Name different types of joins available in SQL?

There are various types of joins in SQL like Left Join, Right Join, Full Join, Cross Join, Natural Join, and many more.


2. What is the difference between a LEFT JOIN and a FULL JOIN?

An SQL LEFT JOIN returns all of the data from the left table, as well as any values from the right table that match. LEFT JOIN returns a NULL value if no match is found. 

An SQL FULL JOIN returns all rows from the connected tables, regardless of whether or not they are matched. It effectively combines the LEFT JOIN and RIGHT JOIN functionalities.

 

3. What is the difference between Joins and UNION clause?

Key Takeaways 

This article discusses how to use the different JOINS using various examples and writing SQL queries. 

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think