SQL Joins
A SQL Joins is 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;
- 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.
- LEFT OUTER JOIN
Syntax: SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;
- RIGHT OUTER JOIN
Syntax: SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2;
- 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;
- RIGHT OUTER JOIN WITH EXCLUSION
Syntax: SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL2
WHERE TABLE1.COL1 IS NULL;
- 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;
- 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 SQL now!!!
© Copyright 2021 | Sevenmentor Pvt Ltd.