Joins

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.

Also Read, LIMIT in SQL

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.

Also see, Tcl Commands in SQL