What is Joins and what are its Types in SQL

 

Joins in SQL

                                                            

JOIN:

JOIN is used to combine records from two or more tables based on a common/related column between them.

TYPES OF JOIN:

The following are the various types of joins in SQL implemented with help of softwares like MS SQL,  MYSQL,  Oracle and may more

 

 Lets Understand joins with the help of Emp and Dept tables

                

                                   Emp Table                                                          Dept Table

1. INNER JOIN:

·         Inner join combines the rows from both the table(table 1 and table 2) that satisfies the mentioned condition on the common column

• ·         Inner join = Intersection of A and B

                                           

                                               

·         Syntax:  SELECT

            << List of columns>>

             FROM

             table1 INNER JOIN table2

             ON

             table1.matching_column = table2.matching_column

 

·         Example: SELECT

                        emp.id,emp.name,emp.deptid,dept.name                

                 FROM

               emp INNER JOIN dept

               ON

               emp.deptid = dept.id

 

Output

 

 

2. LEFT JOIN:

·         The left join keyword returns all records from the left table (table1) and the matched records from the right table (table2).

·         The left join returns all records from the left table even if there are no matches in the right table

                                                    

                                       

 

·         Syntax:  SELECT

            << List of columns>>

             FROM

             table1 LEFT JOIN table2

             ON

             table1.matching_column = table2.matching_column

·         Example: SELECT

                        emp.id,emp.name,emp.deptid,dept.name                

                 FROM

               emp LEFT JOIN dept

               ON

               emp.deptid = dept.id

Output:

 

  

 3. RIGHT JOIN:

.   The right join keyword returns all records from the right table (table2) and the matched records from the left table (table1).

.   The right join returns all records from the right table even if there are no matches in the left table

   

Syntax:  SELECT

            << List of columns>>

             FROM

             table1 RIGHT JOIN table2

             ON

             table1.matching_column = table2.matching_column

·         Example: SELECT

                        emp.id,emp.name,emp.deptid,dept.name                

                 FROM

               emp RIGHT JOIN dept

               ON

               emp.Deptid = dept.id

 

 

 

 

  

 4. FULL JOIN

. The full join keyword  returns all the records from the right and left tables whether they are matched or not.

. full join/full outer join can potentially return very large result-sets!

 

Syntax: SELECT

<< List of colums>>

FROM

table1 FULL JOIN table2

ON

table1.matching_column = table2.matching_column

 

Example: SELECT

emp.id,emp.name,emp.deptid,dept.name

FROM

emp FULL JOIN dept

ON

dept.id = emp.deptid

 

 

 

5. NATURAL JOIN

A Natural join creates an implicit join clause, based on common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

It can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join.

 

 

Syntax:  SELECT

<< List of colums>>

FROM

table1 NATURAL JOIN table2

 

Example: SELECT *

FROM

emp NATURAL JOIN dept

Output:

6. CROSS JOIN

A cross join is used when you want to create a combination of every row from two tables.

Also known as CROSS PRODUCT JOIN or CARTESIAN JOIN.

Syntax:  SELECT

<<List of columns>>

FROM

table 1 CROSS JOIN table 2

Example:  SELECT

emp.name,emp.age,dept.name

FROM

emp CROSS JOIN dept

Output:

 

 

 

 

 

 

 

7. SELF JOIN

A self join is a regular join, where the table is joined with itself. It uses the “where” keyword for condition rather than “on”.

 

SELF JOIN

 

 

Syntax: SELECT

<<List of columns>>

FROM

table1 T1, table1 T2

WHERE

 condition;

 

Example: SELECT

a.ID, b.NAME, a.SALARY

FROM

CUSTOMERS a,

CUSTOMERS b

WHERE

1. SALARY < b. SALARY

 

 

 

 

 

 

 

 

  

8. EQUI JOIN

An equi join is similar to inner join condition. Equi Join creates a join for equality or matching column(s) values of the relative tables. Here, only = operator can be used whereas in inner join comparison operators can be used.

 

EQUI JOIN

 

 

Syntax: SELECT

<<List of columns>>

FROM

table1, table2

WHERE

 table1.matching_column = table2.matching_column

 

Example: SELECT

Emp1.id, emp1.name, emp1.deptid, dept.name

FROM

Emp1, dept

WHERE Dept.id = emp1.deptid

 

 

 

 

 

9. THETA JOIN

Theta join allows us to merge two tables based on the condition represented by Theta(θ). Join columns are not necessarily compared with an equality sign. It produces all combinations of tuples from R1 and R2 that satisfy the join condition.

 

Syntax:

SELECT <<List of columns>> from <Tablename> where R1 ⋈ _{<join condition>} R2

 

Example:

SELECT

name, age , Salary, Deptid

FROM

emp ,dept

WHERE

emp.age > dept.id

 

 

10. STRAIGHT JOIN

Straight Join is similar to JOIN, but in this case the left table is always read first than the right table. It can be used for those cases where the join optimiser puts table in wrong order.

Syntax:

SELECT <<List of columns>> from <Tablename1> STRAIGHT_JOIN <tablename2> on <Condition>

Example:

SELECT

Emp.name, Emp.age , Emp.Salary, Emp.Deptid, Dept.id, Dept.Name

FROM

Emp STRAIGHT_JOIN Dept

ON Emp.Deptid = Dept.id

 

11. ANTI JOIN

Anti joins between two tables returns rows from the first table where there are no matches found in the second table. Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries. It is written with NOT EXISTS or NOT IN constructs. It is opposite of a Semi-Join.

Syntax:

No specific syntax for Anti Join

Example:

SELECT Emp.Deptid

FROM Emp

WHERE Emp.Deptid NOT IN

(SELECT id from Dept);

OR

SELECT * FROM Emp t1 LEFT JOIN Dept t2 ON Emp .id = Dept.id WHERE Dept.id IS NULL

 

12. SEMI JOIN

Semi join provides an efficient method to perform a WHERE EXISTS subquery. It returns one copy of each row from the first table where at least one match is found. The main difference between a semi-join and a conventional join is that rows in the table, left side of the predicate will be returned at most once. Even if the table right side of predicate contains two matches for a row in the table left side of the predicate, only one copy of the row will be returned.

 

Syntax:

Select <columns> FROM <table1> WHERE EXISTS (Select <columns> FROM <table2> WHERE table2.column = tabl21.column )

Example:

SELECT s.id FROM students s WHERE EXISTS (SELECT 1 FROM grades g WHERE g.student_id = s.id)

13. ANTI-SEMI JOIN

Anti-Semi joins are a way to filter a rowset based on the absence of its rows in another rowset.It is of two types Left anti-semi joins and Right Anti-Semi Joins.

• Left Anti-Semi Joins:- The Left Anti Semi Join operator returns each row from the first (top) input when there are no matching rows in the second (bottom) input.
• Right Anti-Semi Joins:- The Right Semi Join operator returns each row from the second (bottom) input when there is a matching row in the first (top) input.

Syntax: No specific keyword

Example:

Select * FROM Customers                  

WHERE NOT EXISTS (SELECT Orders.CustomerID from Orders

WHERE Orders.CustomerID = Customers.CustomerID)

 

 

CONCLUSION

https://www.algaestudy.com/2023/04/13-types-of-joins-in-sql.html

Author:

Anchal Garg, Chithra Nair ,Kaviya u.c,  Shivani Nehra , Tanya Mangath