What is a join
In a real world application, data resides in multiple tables and often you need to fetch data from multiple tables in order to display meaningful information.
Example, suppose there are two tables
Employee: containing basic information about an employee such as his role, designation, department, managerid etc.
Employee_Detail: containing personal information of an employee such as his name, address, blood group, phone number etc.
Rows of both the tables are linked by a column employeeid in the second table which also happens to be the foreign key in the Employee_Detail table referencing the Employee table.
Now when it is required to display the complete data for a particular employee, you need to fetch data from both the tables combined. In other words, you need to join the two tables.
What is Inner join
Inner join is a type of join and is used when you want to fetch rows from two or more tables which have a certain field or more than one field common among them.
That is, an inner join will help you to fetch only those rows whose particular columns(given in query) have the same value in both the tables.
The example given later will make the concept crystal clear. The example given in this article has been executed on MySQL database but it will also work the same on other database types as well.
Syntax
Since a join is used to fetch data from tables, it is added in a SELECT query and its syntax is
SELECT * FROM Table1
JOIN Table2
ON Table1.column_name = Table2.column_name;
This query translates to “Fetch all the rows from Table1 and Table2 for which the value of given column in Table1 matches with the given column in Table2“.
Following points should be remembered regarding the above join query.
- For applying inner join there should be at least one column in each table whose values match with each other.
- You may also write INNER JOIN in place of JOIN. Keyword JOIN implicitly translates to INNER JOIN.
- If the column names on which the join is being applied are different in both the tables, then there is no need to prefix the table names before them.
- Prefixing is only required when the names of columns are same in both tables.
- You may also fetch selective columns, it is not necessary to fetch all columns using *.
- It is permissible to apply more than one conditions in JOIN clause such as
ON Table1.column1 = Table2.column1
AND Table1.column2 > Table2.column2
Inner Join Example
Following are the snapshots of the tables that we will be joining in this example.
Employee
Note that the Employee_Detail table has an employeeid column whose value corresponds to the value of id column in Employee table.
Now if we need to fetch the name, address, role and department of all the employees from the above two tables, then we need to Join both the tables on the basis of employeeid column from Employee_Detail and id column from Employee table.
As per the above stated syntax, SQL query for join will be
Select name, address, role, department from Employee
join Employee_Detail on
Employee.id=Employee_Detail.employeeid;
This query will fetch the required columns from both the tables for the rows whose value of id column in Employee table matches with the value of column employeeid column in Employee_Detail table.
Output of this query will be
Inner join with where clause
A join needs to be applied when you want to fetch data from 2 or more tables. It may also happen that data from 2 or more table needs to be fetched but only those records that match a certain criteria.
In such cases, where records need to be filtered after fetching, a WHERE clause is applied after join. A WHERE clause in join is always applied after JOIN.
In the above example, if we want to fetch employee records for Support department only, then we need to apply a
WHERE clause and the query will then become
SELECT name, address, role, department FROM Employee
JOIN Employee_Detail ON
Employee.id = Employee_Detail.employeeid
WHERE department = ‘Support’;
Implicit inner join
As stated earlier, a join fetches records from 2 or more tables which share column values of one or more columns.
This condition can be applied using a simple WHERE clause which simply compares the values of the columns having common values. In that case, there is no need to use JOIN keyword.
Example, below query will also fetch the same result as the join query.
SELECT name, address, role, department FROM Employee, Employee_Detail
WHERE
Employee.id = Employee_Detail.employeeid;
This query implicitly joins the two tables. Note that the tables that need to be joined are written after FROM keyword and separated by comma and the columns whose values need to be compared are written after WHERE clause.
Table Aliasing
Previously we saw that when there are common columns in the joined tables, then they need to be prefixed with table names and a dot(.)
This is required both with the column names being fetched and the column names being compared(only if their names are common in the tables).
But repeating table names every time can be tedious and can make the query too lengthy. This can be solved by creating an alias of the table name.
An alias is a name that you can give to a table name in the query itself after which, the table can be referenced using this alias only.
An alias can be any user defined name except an SQL keyword. It is given by writing a name just after the name of table.
Example, our join query after adding table aliases will become
SELECT name, address, role, department FROM Employee e
JOIN Employee_Detail ed ON
e.id = ed.employeeid
Where e is the alias for Employee table and ed is the alias for Employee_Detail table.
Aliases can be used even before their declaration. Thus, above query can also be written as
SELECT ed.name, ed.address, e.role, e.department FROM Employee e
JOIN Employee_Detail ed ON
e.id = ed.employeeid
Hope this post explained the concept of inner joins in SQL. Hit the clap to let us know if you liked it.