Join in SQL

[Ettan]

SQL specifies four types of JOIN: INNER, OUTER, LEFT, and RIGHT. The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

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.

JOIN: Return rows when there is at least one match in both tables

Inner join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

[Ettan]

The OUTER JOIN clause differs from the standard JOIN clause (also known as the INNER JOIN clause) in that rows are returned even when there are no matches through the JOIN critieria on the second table.

Use the SQL OUTER JOIN whenever multiple tables must be accessed through a SQL SELECT statement and results should be returned if there is not a match between the JOINed tables.

Right Outer Join

A Right Outer Join will take two tables and join them together based on the linking field.
A Right Outer Join will include ALL entries from the table on the RIGHT.

Syntax

SELECT * FROM table_name1 OUTER table_name2
ON table_name1.column_name = table_name2.column_name

Example

SELECT *FROM   employee RIGHT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Left Outer Join

A Left Outer Join will take two tables and join them together based on the linking field
A Left Outer Join will include ALL entries from the table on the LEFT

Syntax

SELECT * FROM table_name1 LEFT OUTER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Example

 SELECT * FROM   employee  LEFT OUTER JOIN department  
ON employee.DepartmentID = department.DepartmentID