Javatpoint Logo
Javatpoint Logo

SQL Join Example


The user can implement an SQL Join to merge data from two or more tables. In a relational database management system, all the data is related to one another. The data may be stored in different tables but somehow related. The different fields in the tables are composed of entities with relations. The data can even be separated into several tables within a database.

If there is a scenario where the user requires to collect data from several tables simultaneously, then the user can implement a simple Join clause to perform data retrieval. The join clause will merge the fields from different tables that will be associated based on the common data values in each table.

A join clause can be implemented with respect to join prediction. The predicate is mentioned in a WHERE clause. The predicate can be regarded as a condition the tables must meet to merge the data values.

For Example, when performing the equality test between the values in similar columns of different tables. These values may be present in fields with different names. It will also be regarded as a join predicate. The user can implement different operators in a join-predicate. It includes BETWEEN, LIKE, NOT, and comparison operators such as <, >, <>, <=, >=, !=.

SQL provides support for different types of join. The types of Join in SQL can be divided based on how data across the tables are merged. The types of joins are as follows:

Inner Join: An Inner Join returns the intersection of data values in the field of two or more tables. It begins with comparing the data values in the fields of the first table to the data values present in the second table. It then pairs the row and checks if the two rows meet the criteria specified in the join-predicate. It merges the records. It is also known as default join.

Outer Join: It is the opposite of the inner join. It returns all the records even if it has records in the table with no counterpart present in the other table. The outer join can further be classified into three subcategories. These categories are as follows

  • Full Join
  • Left Join
  • Right Join

Syntax of Join In SQL

The syntax to implement the join clause in two tables is as follows:

For Example,

Consider a scenario where the user has two tables in a local database. The database contains records of students and courses provided in the table. Different students are assigned different courses. The first table is the Students table, which contains the personal details of all the students in the college. It has fields for S_ID, S_Name, S_Age, S_City.

S_ID S_Name S_Age S_City
01 Aman 18 Delhi
02 Harsh 22 Ghaziabad
03 Raghav 21 Noida
04 Abhay 22 Jaipur
05 Manav 19 Gurugram
06 Rita 18 Delhi

Implement the below SQL query to create the above table in the database.

To ensure that the above table is successfully inserted into the database, view the table by implementing the below query.

The second table contains related to the course. The fields in the Course table are C_ID, C_Name, SE_ID, and CT_Name.

C_ID C_Name SE_ID CT_Name
110 Mathematics 02 Raman
112 Computer 01 Utkarsh
115 English 03 Vaibhav
121 Psychology 05 Rita

Implement the below SQL commands to create the Courses table.

Implement the below SQL query to view the Courses table.

To merge the Student and Course table data, we will implement the essential Join operation on the two tables using the Join keyword and SELECT clause. The query to join these tables is as follows:

You will get the below result after implementing the above SQL query.


SQL Join Example

The above results are similar to those the user can retrieve by retrieving all the fields from the Students and Courses table. The JOIN keyword has merged the records from both tables into a singular tabular representation.

The user can use another method to implement the same operation by implementing the SELECT query to retrieve the same information.


SQL Join Example

Types Of Joins and its Example

The user can implement two significant types of join. The two types are:

  • Inner Join
  • Outer Join

Example of Inner Join

Inner Join

A common join type enables users to merge two tables based on a standard column. It means that all the columns in the first table are checked with all the columns in the second. It merges all the columns that meet the conditions.

Let's consider a Customers table that contains the CustomerID, CustomerName, and City.

CustomerID CustomerName City
1 Aman Singh Noida
2 Harsh Gupta Ghaziabad
3 Vaibhav Nagpal Jaipur
4 Harshit Sharma Delhi
5 Rita Verma Noida

Now create the second table, the Orders table, that will store the OrderID, CustomerID, Product, and the ordered product quantity. The table is as follows:

OrderID CustomerID Product Quantity
1 1 Phone 2
2 2 Laptop 1
3 1 Tablet 3
4 4 Headphones 1
5 3 SmartWatch 2

Implement the below query to create the above tables and implement the Inner Join.


SQL Join Example

Example of Outer Join

Outer Join

Outer Joins enable the user to have all the records from either one or both tables in the result. There are three types of Outer Join in SQL. The three types include Left, Right, and Full Outer Join.

We will use the above table to implement the Outer Join. The query to implement the outer join is as follows:

It will give the following result:

SQL Join Example

Example of Self Join

Self Join

As the name suggests, it enables the user to merge the table. Consider a situation where you have the employee record and their manager id. Since the manager is an employee, their records are also in the same table.

EmployeeID FirstName LastName ManagerID
1 Aman Verma NULL
2 Harsh Singh 1
3 Saksham Yadav 1
4 Varun Singh 2
5 Guarav Sharma 3
6 Nandini Verma 3


SQL Join Example
Next TopicSQL Not Equal

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA