SQL Joins

SQL Joins:

To retrieve data from multiple tables, the SQL Server allows you to apply JOINS.

USING AN INNER JOIN:

When an inner join is applied, only rows with values satisfying the join condition in the common column are displayed.

A join is implemented by using the SELECT statement, where the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the names of the tables from which combined data is to be retrieved. The WHERE clause specifies the condition, with a comparison operator, based on which the tables will be joined.

The syntax of applying an inner join in the SELECT query is:

                     SELECT column_name1,column_name_2 FROM table1_name JOIN table2_name

                       ON table1_name.ref_column_name join_operator table2_name.ref_column_name

where,

table1_name and table2_name are the names of the tables that are joined

join_operator is the comparison operatror based on which the join is applied

table1_name.ref_column_name & table2_name.ref_column_name are the names of the columns on which the join is applied.

Example:

SELECT e.employeeid, e.title, eph.rate, eph.payfrequency

FROM Employee e JOIN EmployeePayHistory eph ON e.employeeid=eph.employeeid

In the preceding query, the Employee and EmployeePayHistory tables are joined on the common column,employeeid. The query also assigns e as tha alias of the Employee table and eph as the alias of the EmployeePayHistory table. The column names are also listed with the table alias names.

USING AN OUTER JOIN:
In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching rowsfrom another table. For example, if you create an outer join on Table A and Table B, it will show you all the records of Table A and only those records from TableB for which the condition on the common column holds true.

An outer join displays NULL for the columns of the related table where it does not find matching records. The syntax of applying an outer join is:
SELECT column_name, column_name FROM table1_name [LEFT|RIGHT|FULL] OUTER JOIN table2_name ON table1_name.ref_column_name join_operator table2_name.ref_column_name

An Outer join is of three types:
- Left outer join
- Right outer join
- Full outer join

Using a Left Outer Join
A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which the matching rows are not found in the table specified on the right side, NULL values are displayed in the columns that get data from the table specified on the right side.
Using a Right Outer Join
A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.
Using a Full Outer Join
A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matcing rows from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available. 
USING A CROSS JOIN:
A CROSS JOIN, also known as cartesian product, between two tables joins each row from one table with each row of other table.
USING AN EQUI JOIN:
An EQUI JOIN is the same as an inner join and joins tables with the help of a foreign key. However, an equi join is used to display all the columns from both the tables. The common column from all the joining tables is displayed.
USING A SELF JOIN:
In a SELF JOIN, a table is joined with itself. As a result, one row in a table correlates with other rows in the same table. In a self join, a table name is used twice in the query. Therefore, to differentiate the two instances of a single table, the table is given two alias names.