SQL Joins

SQL Joins

Introduction:

Sql joins are used to fetch or retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys. We use these keys relationship in Sql joins. A primary key is a column or a combination of columns with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. The join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

Types of joins: There are different types of join, they are

•    Self Join

•    Inner Join

•    Outer Join

•    Cross Join


INNER JOIN:
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUI JOIN.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Syntax:

Select * FROM table1 
INNER JOIN table2  
ON table1.column_name = table2.column_name;  

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: Key column of the participating tables.

Example: 

SQL> SELECT ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     INNER JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

SELF JOIN: A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement.

Syntax:

SELECT a.column_name, b.column_name...  
FROM table1 a, table1 b  
WHERE a.common_filed = b.common_field; 
Here WHERE clause could be any given expression based on your requirement.

Example:

SQL> SELECT a.ID, b.NAME, a. SALARY
     FROM CUSTOMERS a, CUSTOMERS b
     WHERE a. SALARY < b. SALARY;

OUTER JOIN:
The SQL OUTER JOIN clause is a variation of the SQL JOIN clause enables a SELECT statement to access more than one table. The JOIN clause controls how tables are linked. It is a qualifier of the SQL FROM clause.

The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

The subtypes of SQL OUTER JOIN are,

•    LEFT OUTER JOIN or LEFT JOIN

•    RIGHT OUTER JOIN or RIGHT JOIN

•    FULL OUTER JOIN

Syntax:

Select * 
FROM table1, table2 
WHERE conditions [+]; 

Parameters:


table1, table2: Name of the tables participating in joining.

conditions: Conditions.

LEFT JOIN: The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax: The basic syntax of LEFT JOIN is

Select * 
FROM table1 
LEFT OUTER JOIN table2 
ON table1.column_name=table2.column_name; 

Parameters:


table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:

SQL> SELECT ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

RIGHT JOIN: The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result. But with NULL in each column from left table. This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of RIGHT JOIN is

Select * 
FROM table1<br> 
RIGHT OUTER JOIN table2 
ON table1.column_name=table2.column_name; 

Parameters:


table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:  

SQL> SELECT ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

FULL JOIN: In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the join clause.

Syntax:

Select * 
FROM table1 
FULL OUTER JOIN table2 
ON table1.column_name=table2.column_name; 

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:

SQL> SELECT ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     FULL JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

CROSS JOIN:

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Syntax:


Select * 
FROM table1 
CROSS JOIN table2; 

Parameters:

table1, table2: Name of the tables participating in joining.

Example:


SQL> SELECT ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;

1 comments:

hello sir,
if u have any type of material about ETL TESTING(TERA DATA, INFORMATICA),could you please provide me that material.my id is sureshkumar0519@gmail.com.

Thank you sir

Post a Comment