By using SQL joins , we can retrieve data from two or more tables based on logical relationships between the tables.
SQL JOINS indicate how database should use data from one table to select the rows in others in another table.
The function of performing data from multiple tables is called joining.
Sql can produce data from several related tables by performing either a physical or virtual join of the tables.
The WHERE clause is most often used to perform the JOIN function where two or more tables have common columns.
Specifying the column from each table to be used for JOIN.
A typical join condition specifies a foreign key from one table and its associated key in the other table.
Specifying a logical operator (for example , = or <> ) to be used in comparing values from the columns.
In unrestricted join or Cartesian product of two tables, all possible concatenations are formed of all rows of both the tables.
When we retrive the data from more than one table
- ANSI FORMAT JOINS
- NON ANSI FORMAT JOINS
ANSI FORMAT JOINS
– With “on” key word join condition.
-it is also called new style formant .
Syntax
SELECT */<LIST OF COLUMN NAMES> FROM <FIRST TABLE NAME><JOIN KEY> < SECOND TABLE NAME > ON (JOINING CONDITION)
Type of ANSI FORMAT JOINS
- INNER JOIN
- OUTER JOIN ( left outer, right outer, full
- CROSS JOIN
- NATURAL JOIN
NON ANSI FORMAT JOINS
-Based on “where” key word join condition.
Syntax
SELECT */<LIST OF COLUMN NAMES> FROM <FIRST TABLE NAME><JOIN KEY> < SECOND TABLE NAME > WHERE (JOINING CONDITION)
Type of NON ANSI FORMAT JOINS
- Equi-join
- Non Equi-join
- Self join