SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL 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

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 KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

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

Types of SQL JOIN

The SQL JOIN combines one or more than one tables based on their relationship. The SQL JOIN involves a parent table and a child table relationship.

There are different types of SQL JOINS:

  1. Inner Join
  2. Left Outer Join
  3. Right Outer Join
  4. Full Outer Join
  5. Cross Join.

Let's understand each type of SQL joins with the help of examples.

Inner Join

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

Syntax of SQL Inner Join:

SELECT  Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 INNER JOIN Table_Name2 ON table_Name1.Column_Name = Table_Name2.Column_Name;

Consider the already existing tables, which have the certain data:

Table 1: Employees

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

Table 2: Manager

Manageridmanager_namemanager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING
5Akash KadamC#

Table 3: Laptop:

LAPTOPIDNAMEEMPLOYEEID
L101DELLNULL
L102HP1002
L103LENOVONULL
L104HP3003
L105DELL4002
L106LENOVO4003
L107DELL5001
L108HPNULL
L109DELLNULL
L110HPNULL

Examples of SQL Inner Join

Example 1: Execute a query to join Employee Table and Manager Table and display employees’ details like employee id, employee name, salary from the employee's table, and manager id and manager name from the manager table.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID;

In the above query, we fetched employee id, first name, last name, salary from the employee's table and manager id, manager name from manager table where employees’ table manager id column equals to manager table manager id column. The query will return all the matches records from both tables. Manager id is a common column between both tables. E is an alias name for the employee's table, while M is for the manager table. The manager id field is placed as a foreign key on the employee table. The manager id works as the primary key in the manager table, which creates a parent-child relationship between the two tables.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYMANAGERIDMANAGER_NAME
 1001VAIBHAVIMISHRA655001Sneedeep Kaur
2001PRACHISHARMA555001Sneedeep Kaur
4003RUCHIKAAGARWAL600001Sneedeep Kaur
1003NIKHILVANI505002Kirti kirtane
2002BHAVESHJAIN655002Kirti kirtane
3002ANUJAWANRE505002Kirti kirtane
3001PRANOTISHENDE555003Abhishek Manish
3003DEEPAMJAUHARI585003Abhishek Manish
4002ASHWINIBAGHAT545003Abhishek Manish
4001RAJESHGOUD605004Anupam Mishra
5001ARCHITSHARMA555004Anupam Mishra
1002VAIBHAVSHARMA600005Akash Kadam
2003RUCHIKAJAIN500004Akash Kadam
Types Of SQL JOIN

The records are displayed in ascending order by manager id.

Example 2: Execute a query to join Employee Table and Manager table, and display employee detail’s like employee id, employee name, salary from the employee's table and manager id and manager department from the manager table where employee salary is greater than 58000.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, M.MANAGERID, M.MANAGER_DEPARTMENT FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID WHERE SALARY > 58000;

In the above query, we fetched the employee id, first name, last name, salary from the employee's table, manager id, and manager department from the manager table where the manager id from the employees' table is equal to the manager id from manager table only those employee records whose salary is greater than 58000.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYMANAGERIDMANAGER_DEPARTMENT
 1001VAIBHAVIMISHRA655001ORACLE
4003RUCHIKAAGARWAL600001ORACLE
2002BHAVESHJAIN655002FMW
3003DEEPAMJAUHARI585003JAVA
4001RAJESHGOUD605004TESTING
1002VAIBHAVSHARMA600005C#
Types Of SQL JOIN

Only six records are displayed where an employee's salary exceeds 58000.

Example 3: Execute a query to join the Employees' table and Laptop Table, display employee details like employee id, employee name, and salary, the city from the employee's table, laptop id, and laptop name from the laptop table

SELECT EMP.EMPLOYEEID, EMP.FIRST_NAME, EMP.SALARY, EMP.CITY, LAP.LAPTOPID, LAP. NAME FROM EMPLOYEES EMP INNER JOIN LAPTOP LAP ON EMP.EMPLOYEEID = LAP.EMPLOYEEID;

In the above query, we fetched the employee id, first name, salary, the city from the employees’ table laptop id, and name from laptop table where employee id from employee table equals employee id from laptop table. Employee id is a foreign key in the Laptop table, which creates a parent-child relationship between the Employee table and the Laptop table.

Output:

EMPLOYEEIDFIRST_NAMESALARYCITYLAPTOPIDNAME
1002VAIBHAV60000NOIDAL102HP
3003DEEPAM58500MUMBAIL104HP
4002ASHWINI54500NOIDAL105DELL
4003RUCHIKA60000DELHIL106LENOVO
5001ARCHIT55500DELHIL107DELL
Types Of SQL JOIN

Only five employees' records are displayed.

Example 4: Write a query to display employee id, first name, salary, and department from employees table Manager Id and manager name from manager table using inner join where salary > 55000 and department is Oracle.

SELECT EMPLOYEEID, FIRST_NAME, SALARY, DEPARTMENT, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID WHERE SALARY > 55000 AND DEPARTMENT = 'ORACLE';

In the above join query example, we display the employee's details like id, name, salary, and department from the employee's table, manager id, and manager name from the manager table where the manager id from the employees' table equals the manager id from the manager only those employees whose salary is greater than 55000 and Department is Oracle.

Output:

EMPLOYEEIDFIRST_NAMESALARYDEPARTMENTMANAGERIDMANAGER_NAME
1001VAIBHAVI65500ORACLE1Snehdeep Kaur
2001PRACHI55500ORACLE1Snehdeep Kaur
4003RUCHIKA60000ORACLE1Snehdeep Kaur
Types Of SQL JOIN

Only three employees whose salary is greater than 55000 and the department is Oracle.

Example 5: Execute a join query on the Employees’, Manager and, the Laptop table, and display employees’ details, manager id, and laptop id.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, M.MANAGERID, L.LAPTOPID FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID INNER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

The above query displays employee id, first name, last name, manager id, and laptop id from employees, manager, and laptop tables. We join three tables. Employee id is a common column between employees and the laptop table and creates a parent-child relationship between these three tables. Between employees and managers, table manager id is the common column.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMEMANAGERIDLAPTOPID
4003RUCHIKAAGARWAL1L106
3003DEEPAMJAUHARI3L104
4002ASHWINIBAGHAT3L105
5001ARCHITSHARMA4L107
1002VAIBHAVSHARMA5L102
Types Of SQL JOIN

Left Outer Join

The left outer join will return all the table records on the left side of the join and matching records for the table on the right side of the join. The result-set contains null for the records that are not common on the right side.

Syntax of left outer join: 

SELECT        Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 LEFT OUTER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;

Consider the existing tables, which have the following records:

Table 1: Employees

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

Table 2: Manager

Manageridmanager_namemanager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING
5Akash KadamC#

Table 3: Laptop

LAPTOPIDNAMEEMPLOYEEID
L101DELLNULL
L102HP1002
L103LENOVONULL
L104HP3003
L105DELL4002
L106LENOVO4003
L107DELL5001
L108HPNULL
L109DELLNULL
L110HPNULL

Examples of SQL Left Outer Join

Example 1: Execute a left outer join query on Employees' table and Laptop table, where display employee id, employee name, and laptop id.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, L.LAPTOPID, L.NAME FROM EMPLOYEES E LEFT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

In the above query, we displayed the employee id, first name, last name from the employee's table laptop id, and name from the laptop table and performed a left outer join query on these two tables. The query will return null values on the right side of the table if the rows are not similar between tables.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMELAPTOPIDNAME
1001VAIBHAVIMISHRANULLNULL
1002VAIBHAVSHARMAL102HP
1003NIKHILVANINULLNULL
2001PRACHISHARMANULLNULL
2002BHAVESHJAINNULLNULL
2003RUCHIKAJAINNULLNULL
3001PRANOTISHENDENULLNULL
3002ANUJAWANRENULLNULL
3003DEEPAMJAUHARIL104HP
4001RAJESHGOUDNULLNULL
4002ASHWINIBAGHATL105DELL
4003RUCHIKAAGARWALL106LENOVO
5001ARCHITSHARMAL107DELL
Types Of SQL JOIN

Example 2: Write a query to fetch employee id, first name, last name, salary from employee’s table left outer join on laptop table where salary > 55000.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.LAPTOPID, L.NAME FROM EMPLOYEES E LEFT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID WHERE SALARY > 55000;

In the above query, we fetched the employee details from the employees' table and laptop details from the laptop table, but only those employee details where salary is greater than 55000. We performed the left outer join query on these two tables. The query will return null values on the right side of the table if the rows are not similar between tables.

Output: 

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYLAPTOPIDNAME
1001VAIBHAVIMISHRA65500NULLNULL
1002VAIBHAVSHARMA60000L102HP
2001PRACHISHARMA55500NULLNULL
2002BHAVESHJAIN65500NULLNULL
3001PRANOTISHENDE55500NULLNULL
3003DEEPAMJAUHARI58500L104HP
4001RAJESHGOUD60500NULLNULL
4003RUCHIKAAGARWAL60000L106LENOVO
5001ARCHITSHARMA55500L107DELL
Types Of SQL JOIN

Right Outer Join

The right outer join will return all the table records on the right side of the join and matching records for the table on the left side of the join. The result-set contains null for the records that are not common on the left side.

Syntax of right outer join: 

SELECT        Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 RIGHT OUTER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;

Examples of SQL Right Outer Join        

Example 1: Execute a right outer join query on employees’ table and laptop table, where display employee id, name of the employees’.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, L.LAPTOPID, L.NAME FROM EMPLOYEES E Right OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

In the above query, we displayed the employee id, first name, last name from the employee's table laptop id, and name from the laptop table and performed the right outer join query on these two tables. The query will return null values on the left side of the table if the rows are not similar between tables.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMELAPTOPIDNAME
NULLNULLNULLL101DELL
1002VAIBHAVSHARMAL102HP
NULLNULLNULLL103LENOVO
3003DEEPAMJAUHARIL104HP
4002ASHWINIBAGHATL105DELL
4003RUCHIKAAGARWALL106LENOVO
5001ARCHITSHARMAL107DELL
NULLNULLNULLL108HP
NULLNULLNULLL109DELL
NULLNULLNULLL110HP
Types Of SQL JOIN

Example 2: Write a query to fetch employee id, first name, and last name from the employee's table. Right outer join on laptop table where laptop name includes one of the lists is Dell, HP.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.LAPTOPID, L.NAME FROM EMPLOYEES E RIGHT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID WHERE L.NAME IN ('DELL', 'HP');

In the above query, we displayed the employees’ details from the employee's table laptop details on the laptop table. We performed the right outer join query on these two tables only that employee whose laptop name includes one of the lists is Dell and HP. The query will return null values on the left side of the table if the rows are not similar between tables.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMELAPTOPIDNAME
NULLNULLNULLL101DELL
1002VAIBHAVSHARMAL102HP
3003DEEPAMJAUHARIL104HP
4002ASHWINIBAGHATL105DELL
5001ARCHITSHARMAL107DELL
NULLNULLNULLL108HP
NULLNULLNULLL109DELL
NULLNULLNULLL110HP
Types Of SQL JOIN

Full Outer Join

Full Outer Join merges the result of both the Left Outer Joins and the Right Outer Join. Full Outer Join is the same as Cross Join. Full Outer Joins returns the output, where rows are common or uncommon between the tables.

Syntax of full outer join: 

SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLE1 FULL JOIN TABLE2;

Example 1: Write a query to display employee id, first name, last name from manager id from employee’s table full outer join Manager Table where employee salary is greater than 65000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, M.MANAGERID FROM EMPLOYEES FULL JOIN MANAGER M WHERE SALARY > 65000;

In the above query, we displayed the employees’ details and manager details from the employees and manager table where the employee salary is greater than 65000 using full join. In full, join each Row of the employee's table across each Row of the manager's table, i.e., the Cartesian product will happen. M * N

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMEMANAGERID
1001VAIBHAVIMISHRA1
1001VAIBHAVIMISHRA2
1001VAIBHAVIMISHRA3
1001VAIBHAVIMISHRA4
1001VAIBHAVIMISHRA5
2002BHAVESHJAIN1
2002BHAVESHJAIN2
2002BHAVESHJAIN3
2002BHAVESHJAIN4
2002BHAVESHJAIN5
Types Of SQL JOIN

As we can see, employee id 1001 cross joins with each manager id, same with employee id 2002.

Example 2: Execute a full outer join query on the Employees' table and the laptop table, where employee salary is greater than 60000, and employee location is Pune.

SELECT LAPTOPID, E.EMPLOYEEID, E.FIRST_NAME, E.SALARY, E.CITY FROM LAPTOP FULL JOIN EMPLOYEES E WHERE SALARY > 60000 AND CITY ='PUNE';

In the above query, we display laptop id, employee id, first name, salary, and city from laptop full join employees only those whose salary is greater than 60000 and city is Pune. If both conditions match for employee-only, then the record is fetched.

Output:

Types Of SQL JOIN

Cross Join

Cross joins in SQL are nothing but called a cartesian product. Each Row of one table is combined or merged with each Row of the other table. M * N is cross Join

Syntax of Cross join:

SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLE1 CROSS JOIN TABLE2;

Example 1: Write a query to perform cross join operation on employees table and manager table where employee first name starts with A

SELECT EMPLOYEEID, FIRST_NAME, M.MANAGERID FROM EMPLOYEES CROSS JOIN MANAGER M WHERE FIRST_NAME LIKE 'A%';

In the above full outer join query example, we retrieved the employee details and manager details from the employee table and manager table but only those records where the employee's first name begins with the letter 'A'.

Output:

Types Of SQL JOIN

The result shows that employee id is a cross join with each manager id row.

M * N Here, M = 3; employee id from employee tables

N = 5; manager id from manager table 3 * 5 = 15 rows.

Example 2: Write a query to perform cross join operation on laptop table and employees table where employee salary is greater than 58000 and Mumbai.

SELECT LAPTOPID, NAME, E.EMPLOYEEID FROM LAPTOP CROSS JOIN EMPLOYEES E WHERE SALARY > 58000 AND CITY ='MUMBAI';

In the above query, we displayed the laptop id, name, and employee id from the laptop table to perform cross join operation on the employees' table where the employee salary is greater than 58000 and the city is Pune. Cross join performs like M * N.

Output:

Types Of SQL JOIN



ADVERTISEMENT
ADVERTISEMENT