Sql Joins in Simple Explain 2026 - ShaileshSrn

 SQL JOINS

 

SQL

JOIN clauses are used to combine rows from two or more tables in a relational database, based on a related column between them. Think of it as linking related data (like matching a customer's name from one table with their order details in another) that is stored separately to reduce redundancy. 

We are discus in detail how many types of SQL Joins so let's get start.

 

1. INNER JOIN:  Returns rows when there is a match in both tables.

• The INNER JOINS creates a new results table by combing column values of two tables(table A, table B) based upon the join – predicate. 

• The query compares each row of table1 with each rows of table2 to find all pairs of rows which satisfy the join predicate.

Command of INNER JOIN: - 

 

select e.Emp_id, e.E_fname,e.E_lname, 

d.dept_id, d. dept_name

from employee e

inner join department d

on e.dept_id=d.dept_id;

 

 2. LEFT JOIN:  Returns all rows from the left tables, even if there are no matches in the right table. 

• The LEFT JOIN return all the values from the left table, plus matched values from the left side table or NULL in case of no matching join predicate. 

Example:  

 

select e.Emp_id, e.E_fname, e.E_lname, e.E_salary, 

d.dept_id, d.dept_name

from employee e

left join department d  

on e.dept_id=d.dept_id;

  

Result of LEFT JOIN: 

Emp_id	E_FName	E_LName	E_Salary	Dept_Id	Dept_Name
103	Harry	Potter	20000	12	Resource
104	Edwin	Thomas	15000	12	Resource
105	Steven	Cohen	10000	10	IT
106	Erik	John	12000	11	Marketing

 

 

3.  RIGHT JOIN :  Returns all rows from the left tables, even if there are no matches in the left table. 

• The RIGHT JOIN return all the values from the Right table, plus matched values from the left side table or NULL in case of no matching join predicate. 

  Syntax: - 

  select e.Emp_id, e.E_fname,e.E_lname,e.E_salary, 

  d.dept_id, d.dept_name

  from employee e

  right join department d  # Change this line here 

  on e.dept_id=d.dept_id;

   

   

  Result of RIGHT JOIN: 

  Emp_id	E_FName	E_LName	E_Salary	Dept_Id	Dept_Name
  105	Steven	Cohen	10000	10	IT
  106	Erik	John	12000	11	Marketing
  104	Edwin	Thomas	15000	12	Resource
  103	Harry	Potter	20000	12	Resource
  NULL	NULL	NULL	NULL	13	Shipping

   

 

 

 4. FULL OUTER JOIN: Return rows when there is a match in one of the tables. 

•  The FULL OUTER  JOIN combines the results of both left and right outer joins. The Joined tables contains will all records from both the tables and fill in NULLs for missing matches on either side.  

   select e.Emp_id, e.E_fname,e.E_lname,e.E_salary,

  d.dept_id, d.dept_name

  from employee e

  left join department d #step 1 add left join 

  on e.dept_id=d.dept_id

  union #step 2 add union methods

  select e.emp_id,e.e_fname, e.e_lname, e.E_salary, d.dept_id, d.dept_name

  from employee e

  right join department d #step 3 add right join

  on e.dept_id=d.dept_id;

   

  Result of FULL OUTER : 

   

  Emp_id	E_FName	E_LName	E_Salary	Dept_Id	Dept_Name
  103	Harry	Potter	20000	12	Resource
  104	Edwin	Thomas	15000	12	Resource
  105	Steven	Cohen	10000	10	IT
  106	Erik	John	12000	11	Marketing
  NULL	NULL	NULL	Null	13	Shipping

    

 

 

 5. SELF JOIN:  Used to join a table to itself as if the tables were two tables, temporally at least one tables in the SQL statements. 

• Definition : Assume there are 4 records in table1 and 3 records in table 2 find cross joins methods.   

  Select *from  table1 CROSS JOIN table2;

  Explanation of Cross Join : It is just like that multiple into 1 to all (SET- THEORY). 

   

  Example : Lets see an Example of Employee and Department value of CROSS JOIN  

   

  Table 1* Table2

   

  A	1
  A	2
  A	3
  B	1
  B	2
  B	3
  C	1
  C	2
  C	3
  D	1
  D	2
  D	3

    

   

  Command : select *from department cross join employee;

  
  

  OUTPUT:- 

  Dept_id	Dept_Name	Manager_id	Location _id	Emp_id	E_Fname	E_Lname	E_Salary	EDept_id
  13	Shipping	203	2400	103	Harry	Potter	20000	12
  12	Resource	202	1800	103	Harry	Potter	20000	12
  11	Marketing	201	1500	103	Harry	Potter	20000	12
  10	IT	200	1700	103	Harry	Potter	20000	12
  13	Shipping	203	2400	104	Edwin	Thomas	15000	12
  12	Resource	202	1800	104	Edwin	Thomas	15000	12
  11	Marketing	201	1500	104	Edwin	Thomas	15000	12
  10	IT	200	1700	104	Edwin	Thomas	15000	12
  13	Shipping	203	2400	105	Steven	Cohen	10000	10
  12	Resource	202	1800	105	Steven	Cohen	10000	10
  11	Marketing	201	1500	105	Steven	Cohen	10000	10
  10	IT	200	1700	105	Steven	Cohen	10000	10
  13	Shipping	203	2400	106	Erik	John	12000	11
  12	Resource	202	1800	106	Erik	John	12000	11
  11	Marketing	201	1500	106	Erik	John	12000	11
  10	IT	200	1700	106	Erik	John	12000	11

   

 

6.  CARTESIAN JOIN[CROSS JOIN] :-  Returns the Cartesian product of the set of records from the two or more joined tables.

 

this is end of the course and this is just basic we are discuss in details full so follow me in details.