When the condition is satisfied, column values for each matched pair of rows of two tables are combined into a result row. You can also check out the SQL LEFT JOIN tutorial on this blog.
SQL INNER JOIN
SQL Inner join combines records from two or more tables in a database. The inner join is used to select all rows from tables for the match between the columns in tables.
SQL INNER JOIN is based on the concept of EQUI JOINS. EQUI JOINS are those who use a comparison operator (=) for combining records from two or more tables.
The shaded part above Shows the common records between both the tables.
The inner join is a widely used SQL joins in SQL Database. SQL inner join clause allows us to query the data from two or more related tables. SQL INNER JOIN keyword selects records that have matching values in both tables.
The INNER JOIN keyword selects all the rows from both tables as long as there is a match between the columns. If there are records in the “tableA” table that do not have matches in “tableB,” these rows will not be shown!
Select columns from Table_1 INNER JOIN Table_2 on Table_1.column = Table_2.column;
So, in the above statements, columns represent the column names of the tables.
Table_1 and Table_2 are the names of the tables.
And the condition i.e Table_1.column = Table_2.column is used to compare the columns which are common in both the tables.
In the above syntax, the query has retrieved data from both Table_1 and Table_2 tables:
- First, specify the main table (Table_1) in the FROM clause.
- The second table contains an INNER JOIN clause (Table_2) and a join predicate. The only rows that cause a join predicate to evaluate TRUE are included in a result set.
The INNER JOIN clause compares each row of the table Table_1 with rows of Table_2 to find all the pairs of rows that satisfy a join predicate.
If a join predicate evaluates to TRUE, the column values of the matching rows of Table_1 and Table_2 are combined into a new row and included in the result set.
Let’s see an example of the above statements.
Now, the query for the inner join is the following.
Select Department.Dept_no, Employee.Emp_name, Employee.City, Employee.Salary from Department INNER JOIN Employee on Department.Emp_id = Employee.Emp_id
See the output below.
As you can see that the common records between the columns of employee and department table are shown.
JOIN Three Tables
See the following SQL INNER JOIN Query.
Select Department.Dept_no, Employee.Emp_name, Employee.City, Job.Job from((Department INNER JOIN Employee ON Department.Emp_id = Employee.Emp_id) INNER JOIN Job ON Department.Dept_no = Job.Dept_id);
Here, the condition is checked between the department and employee table, and then it is checked between the Department and Job table. Then the common records between all those tables are displayed.
We are displaying all columns of the Employee and Department table using INNER JOIN.
Select * from Employee INNER JOIN Department ON Department.Emp_id = Employee.Emp_id;
See the below output.
- SQL is not Case Sensitive.
- While checking for equality conditions, column names of both tables don’t need to be the same, and they can be different. So the only thing checked is a record within those particular columns.
- The INNER JOIN in SQL joins two tables according to matching specific criteria using a comparison operator.
Difference between INNER JOIN and OUTER JOIN
The INNER JOIN is such type of join that returns all the rows from both the participating tables where a key record of one table is equal to the key records of another table.
Inner Join required a comparison operator to match rows from the participating tables based on both tables’ common field or column.
Whereas an OUTER JOIN returns all the rows from the participating tables that satisfy the condition, those rows that do not match the condition will appear in this operation. This result set can appear in three types of format –
The first one is the LEFT OUTER JOIN. This join output includes all the rows from the left table of the JOIN clause and unmatched rows from the right table with NULL values for selected columns.
The second one is the RIGHT OUTER JOIN. In this join, the output includes all the rows from the right of the JOIN cause and unmatched rows from a left table with NULL values for selected columns.
The last one is in FULL OUTER JOIN. This join includes the matching rows from the left and right tables of the JOIN clause and the unmatched rows from the left and the right table with NULL values for selected columns.
That’s it for this tutorial.