SQL Join Example

SQL JOIN

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_IDS_NameS_AgeS_City
01Aman18Delhi
02Harsh22Ghaziabad
03Raghav21Noida
04Abhay22Jaipur
05Manav19Gurugram
06Rita18Delhi

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_IDC_NameSE_IDCT_Name
110Mathematics02Raman
112Computer01Utkarsh
115English03Vaibhav
121Psychology05Rita

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.

Output:

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.

Output:

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.

CustomerIDCustomerNameCity
1Aman SinghNoida
2Harsh GuptaGhaziabad
3Vaibhav NagpalJaipur
4Harshit SharmaDelhi
5Rita VermaNoida

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:

OrderIDCustomerIDProductQuantity
11Phone2
22Laptop1
31Tablet3
44Headphones1
53SmartWatch2

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

Output:

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.

EmployeeIDFirstNameLastNameManagerID
1AmanVermaNULL
2HarshSingh1
3SakshamYadav1
4VarunSingh2
5GuaravSharma3
6NandiniVerma3

Output:

SQL Join Example
Next TopicSQL Not Equal




Latest Courses