SQL Joins

  • By
  • January 25, 2023
  • SQL
SQL Joins

SQL Joins

A JOIN clause is used to combine rows from multiple tables, based on the related column between them which carries a very important status in databases.

 

Let us try to explore SQL Joins; the following are the types of SQL Joins.

  • Natural Join :

This type of join automatically matches rows from both tables based on their related columns. The related column must have the same name and data type in both tables.

 

Syntax: SELECT * FROM TABLE1 NATURAL JOIN TABLE2;

 

  1. INNER JOIN 

Syntax: SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;

 

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

 

  • Outer Join :

This type of join is similar to CROSS APPLY, but it returns all rows from the left table, even if there are no matching rows in the right table.

 

  1. LEFT OUTER JOIN

Syntax: SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;

 

  1. RIGHT OUTER JOIN 

Syntax: SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;

 

  1. FULL OUTER JOIN 

Syntax: SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;

 

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

 

  • CROSS JOIN 

This type of join allows applying a table valued function to each row in a table and joining the results to the input rows.

 

Syntax: SELECT * FROM TABLE1 CROSS JOIN TABLE2 CROSS JOIN TABLE3;

 

  1. RIGHT OUTER JOIN WITH EXCLUSION 

Syntax:   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2

        WHERE TABLE1.COL1 IS NULL; 

 

  1. LEFT OUTER JOIN WITH EXCLUSION 

Syntax:   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2

        WHERE TABLE2.COL1 IS NULL; 

 

Note: Best SQL Course in Pune to Learn in Online / Classroom Format and Get Certified from the Best SQL Training Institute in Pune SevenMentor Training Institute.

 

  • SELF JOIN

Syntax: SELECT * FROM TABLE T1 INNER JOIN TABLE T2 

ON T1.COL = T2.COL;

 

  1. FULL OUTER JOIN WITH EXCLUSION 

Syntax:  SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2

 ON TABLE1.COL1 = TABLE2.COL2 

 WHERE TABLE1.COL1 IS NULL OR TABLE2.COL1 IS NULL;

 

  • TWO INNER JOIN 

This type of join returns all rows from all three tables that have matching values in the joined columns. It will first join table1 and table2 based on the values in the col1 and col2 columns. 

 

Syntax: SELECT * FROM TABLE1 INNER JOIN TABLE2

ON TABLE1.COL1 = TABLE2.COL2

LEFT JOIN TABLE3 ON TABLE2.COL3 = TABLE3.COL4;

 

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

 

  • TWO LEFT OUTER JOINS

It returns all rows from the leftmost table, as well as any matching rows from the other two tables. Rows from the other two tables that do not have a match in the leftmost table are NULL.

 

Syntax: SELECT * FROM TABLE1 LEFT JOIN TABLE2

ON TABLE1.COL1 = TABLE2.COL2

LEFT JOIN TABLE3 ON TABLE2.COL3 = TABLE3.COL4;

 

Author:-

Aniket Kulkarni

Call the Trainer and Book your free demo Class for JAVA now!!!

© Copyright 2021 | Sevenmentor Pvt Ltd.

 

Submit Comment

Your email address will not be published. Required fields are marked *

*
*