Which of the given joint will combine rows from different tables if the join condition is true?

What is Inner Join in SQL?

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

Pictorial presentation of SQL Inner Join:

Syntax:

OR

The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Syntax diagram - INNER JOIN

Example: SQL INNER JOIN between two tables

Here is an example of inner join in SQL between two tables.

Sample table: foods

Sample table: company

To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition -

1. company_id of foods and company table must be same,

the following SQL statement can be used :

SQL Code:

Output:

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition -

1. company id of foods and company id of company table must be same,

the following SQL statement can be used:

SQL Code:

Output:

Pictorial Presentation of SQL Inner Join of Company and Foods Tables:

SQL INNER JOIN for all columns

To get all the columns from foods and company table after joining, with the following condition -

1. company id of foods and company id of company table must be same,

the following SQL statement can be used:

SQL Code:

Output:

Difference between JOIN and INNER JOIN

JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same for both the students and courses tables.

Using JOIN Clause

Using INNER JOIN Clause

Difference between INNER JOIN and OUTER JOIN

An INNER JOIN is such type of join that returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.

Where as the OUTER JOIN returns all rows from the participating tables which satisfy the condition and also those rows which do not match the condition will appear in this operation. This result set can appear in three types of format -

The first one is LEFT OUTER JOIN, in this join includes all the rows from a left table of JOIN clause and the unmatched rows from a right table with NULL values for selected columns.

The second one is RIGHT OUTER JOIN, in this join includes all rows from the right of JOIN cause and the unmatched rows from the left table with NULL values for selected columns.

The last one in FULL OUTER JOIN, in this join, includes the matching rows from the left and right tables of JOIN clause and the unmatched rows from left and right table with NULL values for selected columns.

Example:

Here is two table tableX and tableY and they have no duplicate rows in each. In tableX the values ( A,B) are unique and in tableY the values (E,F) are unique, but the values (C and D) are common in both the tables.

Here is INNER JOIN

or

Output:

Here only the matching of both tableX and tableY have appeared in the result set.

Here is LEFT OUTER JOIN

or

Output:

Here all the rows from tableX that is left side of JOIN clause and all the rows with NULL values for unmatched columns from tableY that is the right side of JOIN clause have appeared.

Here is RIGHT OUTER JOIN

Output:

Here all the rows from tableY that is the right side of JOIN clause and all the rows with NULL values for unmatched columns from tableX that is left side of JOIN clause have appeared.

Here is FULL OUTER JOIN

Output:

Here all the matching rows from tableX and tableY and all the unmatched rows with NULL values for both the tables have appeared.

INNER JOIN ON vs WHERE clause

The WHERE clause, what is done is that all records that match the WHERE condition are included in the result set but an INNER JOIN is that, data not matching the JOIN condition is excluded from the result set.

Linking between two or more tables should be done using an INNER JOIN ON clause but filtering on individual data elements should be done with WHERE clause.

INNER JOIN is ANSI syntax whereas the WHERE syntax is more relational model oriented.

The INNER JOIN is generally considered more readable and it is a cartesian product of the tables, especially when you join lots of tables but the result of two tables JOIN'ed can be filtered on matching columns using the WHERE clause.

INNER JOINS: Relational Databases

• Oracle INNER JOIN
• MySQL INNER JOIN
• SQLite INNER JOIN
• PostgreSQL INNER JOIN

Key points to remember

Click on the following to get the slides presentation -

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL NON EQUI JOIN
Next: SQL NATURAL JOIN