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

EidEnameAgeCitySalary
E001ABC29Pune20000
E002PQR30Pune30000
E003LMN25Mumbai5000
E004XYZ24Mumbai4000
E005STU32Bangalore25000

<Department> Table

DeptidEidDeptName
D001E001Finance
D002E002Production
D003E003Sales
D004E005Marketing
D005E005Human Resource

Types of JOINS:

Join TypeDescriptionSyntaxExample
Simple JoinIt 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 JoinIt 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 JoinIt 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 JoinIt 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>;
▲