SQL join with example

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

1.   ANSI FORMAT JOINS
2. 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

1. INNER JOIN
2. OUTER JOIN ( left outer, right outer, full
3. CROSS JOIN
4. 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

1. Equi-join
2. Non Equi-join
3. Self join