SQL Joins

Introduction to Joins

Combining the records from two or more tables in a relational database is called as Join.
Join is used to match or equate different fields from two or more tables using a primary key or a foreign key.
It combines records from two tables.
It creates a set that can be saved as a table or used as it is.

Example:
<Employee> Table

Eid	Ename	Age	City	Salary
E001	ABC	29	Pune	20000
E002	PQR	30	Pune	30000
E003	LMN	25	Mumbai	5000
E004	XYZ	24	Mumbai	4000
E005	STU	32	Bangalore	25000

<Department> Table

Deptid	Eid	DeptName
D001	E001	Finance
D002	E002	Production
D003	E003	Sales
D004	E005	Marketing
D005	E005	Human Resource

Types of JOINS:

Join Type	Description	Syntax	Example
Simple Join	It finds name and department name of students who have been allotted a department.	SELECT <list_of_column> FROM <table_name1>, <table_name2> WHERE <table_name1>. <column_name1> = <table_name2>. <column_name1>	SELECT Ename, DeptName FROM Employee, Department WHERE Employee.Eid = Department.Eid;
Inner/ Equi / Natural Join	It extracts data that meets the join conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.	SELECT <list_of_column> FROM <table_name1> INNER JOIN <table_name2> WHERE <table_name1>. <column_name1> = <table_name2>. <column_name1>	SELECT * FROM Employee INNER JOIN Department WHERE Employee.Eid=Department.Eid;
Outer Join	It includes non matching rows.	SELECT DISTINCT <list_of_column> FROM <table_name1> LEFT OUTER JOIN <table_name2> WHERE <table_name1>. <column_name1> = <table_name2>. <column_name1>	SELECT DISTINCT * FROM Employee LEFT OUTER JOIN Department WHERE Employee.Eid=Department.Eid
Self Join	It joins a table to itself.	SELECT DISTINCT <list_of_column> FROM <table_name1> AS <alias_name1> <table_name1> AS <alias_name2> WHERE <alias_name1>.<column_name1> = <alias_name2>.<column_name1>ORDER BY <alias_name1>.<column_name1>, <alias_name1>.<column_name2>;	SELECT DISTINCT e1.Ename, e1.City, e1.Salary, e1.Eid FROM Employee AS e1, Employee AS e2 WHERE e1.Eid = e2.Eid ORDER BY e1.Eid, e1.Ename;

Alias

Alias is a shorthand for a table name or a column name.
It reduces the amount of typing required to enter a query.
Using alias, complex queries are easier to read.
The alias name is only exists for the duration of the query.
Alias names are useful for Aggregate functions and Joins.

Syntax:
SELECT <column_name> AS <alias_name>
FROM <table name> <alias_name> WHERE <condition>;