SQL Joins Types

SQL Joins

It is crucial to learning the concepts and language for performing joins. Different type of joins is introduced in their primitive forms outlining the broad categories that are available. An in-depth discussion of the various join clauses is then conducted.

Types of joins

Two basic joins are the equijoin and the non-equijoin. Equijoins are more frequently used. Joins may be performed between multiple tables, but much of the following The first table is called the source and the second is called the target. Rows in the source and target tables comprise one or more columns

EQUI JOINS OR INNER JOIN OR NATURAL JOIN OR JOIN

The EQUI JOIN (also called an inner or regular join) syntax indicates the columns that comprise the JOINS between two tables. WHERE clause by stating which values in a set of columns from one table are equal to the values in a set of columns from another table.

Syntax

SELECT table1.column1, table1.column3, table3.column1,....
FROM table1
INNER JOIN table3
ON table1.matching_column = table3.matching_column;

NATURAL JOINS:

The NATURAL JOIN is another part of the ANSI 1999 syntax that you can use when joining two tables based on columns that have the same name and datatype. The NATURAL JOIN can be used only when all columns that have the same name in both tables comprise the join condition between these tables. You cannot use this syntax when the two columns have the same name but a different datatype. Another benefit of this join is that if you use the

SELECT * syntax, the columns that appear in both tables appear only once in the result set.

Natural Joins

OUTER JOINS:

LEFT JOIN OR RIGHT JOIN is the result of joining two tables that contain rows where a match occurred on the join condition. because only those rows that match on the join condition appear in the final result set.

The result set of an OUTER JOIN contains the same rows as the INNER JOIN plus rows corresponding to the rows from the source tables where there was no match.  It was not a part of the ANSI standard until the 1999 version.

Oracle’s OUTER JOIN syntax has consisted of placing a plus sign (+) next to the columns of a table where you expect to find values that do not exist in the other table.

Syntax

SELECT table1.column1, table1.column3, table3.column1....
FROM table1
LEFT JOIN table3
ON table1.matching_column = table3.matching_column;

LEFT OUTER JOIN:

Left Outer Join

RIGHT OUTER JOIN:

Right Outer Join

CROSS JOIN:

The result set of a Cartesian product usually is meaningless, but it can be used to generate a lot of rows if you need to do some testing. The advantage of the new syntax is that it flags a Cartesian product by having the CROSS JOIN in the FROM clause.

Cross Join

SELF-JOIN:

A table is joined with itself

Self Join

Related BLogs:

Leave a Reply

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

Looking for Online Training