SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL
SQL JOIN
2022-06-25 02:10:05

SQL JOIN

As the name says, joins mean to merge something or to combine something. But in the case of SQL, joins mean to merge or combine two different tables.

The Join clause uses in two tables, which are the parent and child tables. Join clause takes records from one or more tables and combines the records.

There are different types of joins used in SQL are as follows:

1. Inner Join

2. Left Outer Join

3. Right Outer Join

4. Full Outer Join

SQL Joins takes records from two different tables but displays results in a single table.

Let's understand each type of join one by one with the example.

1. Inner Join

Inner Join in SQL is a widely used join. It takes all the records from both tables until and unless the conditions match. It means this join will return only those common rows in both tables.

The syntax of the inner join is as follows:

SELECT Table_Name_1.Column_Name_1, Table_Name_1.Column_Name_2, Table_Name_2.Column_Name_1, Table_Name_2.Column_Name_2 FROM Table_Name_1 INNER JOIN Table_Name_2 ON Table_Name_1.Column_Name = Table_Name_2.Column_Name;

 Let’s understand inner join more by looking at an example:

We have two tables:

Table Name 1:  Employee_Details table with certain columns

E_id                E_NameE_salaryE_City 
1001                    Pranoti Shende60000    Pune  
1002                  Vaibhav Sharma58000Mumbai
1003                        Nikhil Vani50000Mumbai
1004              Prachi Sharma60000    Hyderabad
1005Harshada Koli48500Nashik
1006Sonal Maheshwari50000Bangalore
1007Bhavesh Jain65000Pune
1008Kapil Verma50000Nashik
1009Rajesh Goud55000Hyderabad
1010Deepam Jauhari60000Bangalore

Table Name 2: Comp

Table with certain columns (E_Id Common in both tables)

 Comp_Id         MakeE_Id
101Dell1001
102Dell1004
103 Lenovo1006
104Lenovo1008
105 HP1010
106HP1005
107Asus1003

We want to display employee Id, names, and make from the comp table.

To produce this as output, execute this query as follow:

SELECT ED.E_Id, ED.E_Name, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id;

The output of the above query is as follows:

E_id                E_NameMake
1001                    Pranoti ShendeDell
1004              Prachi SharmaDell
1006Sonal MaheshwariLenovo
1008Kapil VermaLenovo
1010Deepam JauhariHP
1005Harshada KoliHP
1003                        Nikhil VaniAsus
SQL JOIN

Suppose, while using inner joins, there can be a situation where you want to filter rows based on some criteria. In such a case, we can use the where clause.

Let's take an example and understand the example of how where clause works in joins:

SELECT ED.E_Id, ED.E_Name, ED.E_City, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id WHERE E_City = ‘Bangalore’;

We display the employee's information whose employee resides in Bangalore City in the above query.

The output of the above query is as follows:

E_id                E_NameE_CityMake
1006Sonal MaheshwariBangaloreLenovo
1010Deepam JauhariBangaloreHP
SQL JOIN

For example, you want to display employee id, name, city and salary, comp id and make where group by comp Id; then we will execute the below query as follow:

SELECT ED.E_Id, ED.E_Name, ED.E_Salary, ED.E_City, C.Comp_Id, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id GROUP BY Comp_Id;

The output of the above query is as follows:

E_id                E_NameE_salaryE_City  Comp_Id         Make
1001                    Pranoti Shende60000    Pune  101Dell
1004              Prachi Sharma60000Hyderabad102Dell
1006Sonal Maheshwari50000Bangalore103 Lenovo
1008Kapil Verma50000    Nashik104Lenovo
1010Deepam Jauhari60000Bangalore105 HP
1005Harshada Koli48500Nashik106HP
1003                        Nikhil Vani50000Mumbai107Asus
SQL JOIN

2. Left Outer Join

The left outer join returns all the records from (left table) table one, whether the record in table 2 matches or not, according to the join condition. The record, which matches their result set is the same as the inner join result, and all uncommon records from another table will result in null.

Let’s understand Left Outer join more by looking at an example:

We have two tables:

Table Name 1:  Employee_Details table with certain columns

E_id                E_NameE_salaryE_City 
1001                    Pranoti Shende60000    Pune  
1002                  Vaibhav Sharma58000Mumbai
1003                        Nikhil Vani50000Mumbai
1004              Prachi Sharma60000    Hyderabad
1005Harshada Koli48500Nashik
1006Sonal Maheshwari50000Bangalore
1007Bhavesh Jain65000Pune
1008Kapil Verma50000Nashik
1009Rajesh Goud55000Hyderabad
1010Deepam Jauhari60000Bangalore

Table Name 2: Comp

Table with certain columns (E_Id Common in both tables)

 Comp_Id         MakeE_Id
101Dell1001
102Dell1004
103 Lenovo1006
104Lenovo1008
105 HP1010
106HP1005
107Asus1003

We want to display the employee’s name, id from the employee's table and make a name, and comp id from the comp table

To produce this as output, execute this query as follow:

SELECT ED.E_Id, ED.E_Name, C.Comp_Id, Make FROM Employee_Details ED LEFT JOIN Comp C ON ED.E_Id = C.E_Id;

The output of the above query is as follows:

E_id                  E_NameComp_IdMake
1001                    Pranoti Shende101Dell
1002                  Vaibhav SharmaNULLNULL
1003                        Nikhil Vani107Asus
1004              Prachi Sharma102Dell
1005Harshada Koli106HP
1006Sonal Maheshwari103Lenovo
1007Bhavesh JainNULLNULL
1008Kapil Verma104Lenovo
1009Rajesh GoudNULLNULL
1010Deepam Jauhari105HP
SQL JOIN

Suppose, while using left outer joins, there can be a situation where you want to filter rows based on some criteria. In such cases, we can use the where clause.

Let's take an example and understand the example of how where clause works in joins:

SELECT ED.E_Id, ED.E_Name, ED.E_Salary, C.Comp_Id, Make FROM Employee_Details ED LEFT JOIN Comp C ON ED.E_Id = C.E_Id WHERE E_Salary BETWEEN 50000 AND 60000;

In the above query, we display the employee's information whose employee salary is between 50000 and 60000.

The output of the above query is as follows:

E_id                  E_NameE_salaryComp_IdMake
1001                    Pranoti Shende60000    101Dell
1002                  Vaibhav Sharma58000NULLNULL
1003                        Nikhil Vani50000107Asus
1004              Prachi Sharma60000    102Dell
1006Sonal Maheshwari50000103Lenovo
1008Kapil Verma50000104Lenovo
1009Rajesh Goud55000NULLNULL
1010Deepam Jauhari60000105HP
SQL JOIN

3. Right Outer Join

In Right Outer, join for both tables will return all the records from table 2, whether the record in table 1 matches or not to the join condition. The record which matches their result set is the same as the inner join result, and all nonmatching records from another table will result in null.

Let’s understand Right Outer join more by looking at an example:

We have two tables:

Table Name 1:  Employee_Details table with certain columns

E_id                E_NameE_salaryE_City 
1001                    Pranoti Shende60000    Pune  
1002                  Vaibhav Sharma58000Mumbai
1003                        Nikhil Vani50000Mumbai
1004              Prachi Sharma60000    Hyderabad
1005Harshada Koli48500Nashik
1006Sonal Maheshwari50000Bangalore
1007Bhavesh Jain65000Pune
1008Kapil Verma50000Nashik
1009Rajesh Goud55000Hyderabad
1010Deepam Jauhari60000Bangalore

Table Name 2: Comp

Table with certain columns (E_Id Common in both tables)

 Comp_Id         MakeE_Id
101Dell1001
102Dell1004
103 Lenovo1006
104Lenovo1008
105 HP1010
106HP1005
107Asus1003

We want to display the employee’s name, id from the employee's table and make a name, and comp id from the comp table

To produce this as output, execute this query as follow:

SELECT C.Comp_Id, Make, ED.E_Id, ED.E_Name FROM Comp C RIGHT OUTER JOIN Employee_Details ED ON ED.E_Id = C.E_Id;

The output of the above query is as follows:

Comp_IdMakeE_id                E_Name
101Dell1001                    Pranoti Shende
NULLNULL1002                  Vaibhav Sharma
107Asus1003                        Nikhil Vani
102Dell1004              Prachi Sharma
106HP1005Harshada Koli
103Lenovo1006Sonal Maheshwari
NULLNULL1007Bhavesh Jain
104Lenovo1008Kapil Verma
NULLNULL1009Rajesh Goud
105HP1010Deepam Jauhari
SQL JOIN

Suppose, while using right outer joins, there can be a situation where you want to filter rows based on some criteria. In such cases, we can use the where clause.

Let's take an example and understand the example of how where clause works in joins:

SELECT C.Comp_Id, Make, ED.E_Id, ED.E_Name, ED.E_Salary FROM Comp C RIGHT OUTER JOIN Employee_Details ED ON ED.E_Id = C.E_Id WHERE E_Salary BETWEEN 50000 AND 60000;

In the above query, we display the employee's information whose employee salary is between 50000 and 60000.

The output of the above query is as follows:

Comp_IdMakeE_id                  E_NameE_salary
101Dell1001                    Pranoti Shende60000    
NULLNULL1002                  Vaibhav Sharma58000
107Asus1003                        Nikhil Vani50000
102Dell1004              Prachi Sharma60000    
103Lenovo1006Sonal Maheshwari50000
104Lenovo1008Kapil Verma50000
NULLNULL1009Rajesh Goud55000
105HP1010Deepam Jauhari60000
SQL JOIN

4. Full Outer Join

Full Outer Join merges the result of both outer joins, Left Outer Joins and the Right Outer Join. Full Outer Joins returns the output, which matches rows and unmatched rows between the two tables. Full Outer Join is the same as Cross Join.

Let’s understand Full Outer join more by looking at an example:

We have two tables:

Table Name 1:  Employee_Details table with certain columns

E_id                E_NameE_salaryE_City 
1001                    Pranoti Shende60000    Pune  
1002                  Vaibhav Sharma58000Mumbai
1003                        Nikhil Vani50000Mumbai
1004              Prachi Sharma60000    Hyderabad
1005Harshada Koli48500Nashik
1006Sonal Maheshwari50000Bangalore
1007Bhavesh Jain65000Pune
1008Kapil Verma50000Nashik
1009Rajesh Goud55000Hyderabad
1010Deepam Jauhari60000Bangalore

Table Name 2: Comp

Table with certain columns (E_Id Common in both tables)

 Comp_Id         MakeE_Id
101Dell1001
102Dell1004
103 Lenovo1006
104Lenovo1008
105 HP1010
106HP1005
107Asus1003

We want to display all the details from the employee_details and comp tables where salary is between 55000 and 60000. We will use the below query:

SELECT * FROM Employee_Details FULL JOIN Comp WHERE E_Salary BETWEEN 55000 AND 60000;

The output of the above query is as follows:

E_id                E_NameE_salaryE_City  Comp_Id        MakeE_Id
1001                    Pranoti Shende60000    Pune  101Dell1001
1001                    Pranoti Shende60000    Pune  102Dell1004
1001                    Pranoti Shende60000    Pune  103Lenovo1006
1001                    Pranoti Shende60000    Pune  104Lenovo1008
1001                    Pranoti Shende60000    Pune  105HP1010
1001                    Pranoti Shende60000    Pune  106HP1005
1001                    Pranoti Shende60000    Pune  107Asus1003
1002Vaibhav Sharma58000Mumbai101Dell1001
1002Vaibhav Sharma58000Mumbai102Dell1004
1002Vaibhav Sharma58000Mumbai103Lenovo1006
1002Vaibhav Sharma58000Mumbai104Lenovo1008
1002Vaibhav Sharma58000Mumbai105HP1010
1002Vaibhav Sharma58000Mumbai106HP1005
1002Vaibhav Sharma58000Mumbai107Asus1003
1004Prachi Sharma60000Hyderabad101Dell1001
1004Prachi Sharma60000Hyderabad102Dell1004
1004Prachi Sharma60000Hyderabad103Lenovo1006
1004Prachi Sharma60000Hyderabad104Lenovo1008
1004Prachi Sharma60000Hyderabad105HP1010
1004Prachi Sharma60000Hyderabad106HP1005
1004Prachi Sharma60000Hyderabad107Asus1003
1009Rajesh Goud55000Hyderabad101Dell1001
1009Rajesh Goud55000Hyderabad102Dell1004
1009Rajesh Goud55000Hyderabad103Lenovo1006
1009Rajesh Goud55000Hyderabad104Lenovo1008
1009Rajesh Goud55000Hyderabad105HP1010
1009Rajesh Goud55000Hyderabad106HP1005
1009Rajesh Goud55000Hyderabad107Asus1003
1010Deepam Jauhari60000Bangalore101Dell1001
1010Deepam Jauhari60000Bangalore102Dell1004
1010Deepam Jauhari60000Bangalore103Lenovo1006
1010Deepam Jauhari60000Bangalore104Lenovo1008
1010Deepam Jauhari60000Bangalore105HP1010
1010Deepam Jauhari60000Bangalore106HP1005
1010Deepam Jauhari60000Bangalore107Asus1003
SQL JOIN