SQL Joins Types
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.
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:
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.
SELF-JOIN:
A table is joined with itself
+1 201-949-7520
+91-9707 240 250