SQL Inner Join Keyword | What is inner join in SQL with Example

In these articles, you will learn how to use the SQL INNER JOIN clause to query data from multiple tables,
The SQL Inner Join is the most commonly used Joins in SQL Server, The inner join SQL clause allows you to query data from two or more tables. The INNER JOIN SQL keyword selects records that have matching values in both tables and returns the records that have matching values in both or related multiple tables.

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

Syntax: SQL Inner Join

OR

The following shows the syntax of  SQL INNER JOIN clause the query retrieved data from both table1 and table2 tables:

furthermore, first, specify the main table (table1) in the FROM clause and second, specify the second table in the INNER JOIN SQL clause (table2) and a join predicate. Therefore, only rows that cause the join predicate to evaluate to TRUE are included in the result set.

The SQL INNER JOIN clause compares each row of the table (table1) with rows of the table (table2) to find them all pairs of rows that satisfy the join predicate. Furthermore, If the join predicate evaluates to TRUE, the column values of the matching rows of table1 and table2 are combined into a new row and included in the result set.

There are the given following table illustrates the inner join of two tables like table1 (A, B, C) and table2 (1,2,3). The result includes rows: (B,2) and (C,2) as they have the same color pattern patterns.

INNER JOIN between two tables

Therefore, let’s see the example of inner join in SQL between two tables, here we take two demo tables such as table1 for DoctorDetails and table2 for Department.
Let us first create a table DoctorDetails with the given below query.

Demo Table :DoctorDetails

Therefore, let us create a Second table name as Department with the given below query.

Demo Table: Department

Furthermore, to join them Doctor Details Columns from DoctorDetails table and Hospital Name, hospital city, salary ID from the Department table, with the following condition:
1. HospitalId of DoctorDetails and Department table must be the same.
Therefore, there are the following SQL statement can be used :

SQL Code:

Output:

SQL INNER JOIN using JOIN keyword

Therefore, let’s take an example SQL INNER JOIN using JOIN keywords, To get Doctor Name, doctor unit columns from DoctorDetails table and Hospital Name, city columns from Department table, after joining these mentioned tables, with the following condition –
1. the HospitalId of DoctorDetails and Department table must be the same.
the given below the following SQL statement can be used :

SQL Code:

Output:

INNER JOIN SQL for all columns

Therefore, to get all the columns from DoctorDetails and Department table after joining, with the following condition –
1. the HospitalId of DoctorDetails and Department table must be the same.
the given below the following SQL statement can be used :

SQL Code:

Output:

Difference between JOIN SQL and INNER JOIN

The SQL INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.
Therefore, an SQL INNER JOIN is the same as the JOIN clause, the combining rows from two or more related tables.
JOIN SQL returns all rows from tables where the key record of one table is equal to the key records of another table.
Inner joins SQL 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 course tables.
furthermore, an inner join of X and Y gives the result of X intersect Y, i.e. the inner part of a Venn diagram intersection.

Syntax: When Using INNER JOIN Clause

Syntax: Using JOIN Clause

WHERE clause vs INNER JOIN ON

WHERE clause	INNER JOIN ON
The WHERE clause: what is done is that all data records that match the WHERE condition are included in the result set	Therefore, an INNER JOIN SQL is that data not matching the SQL JOIN condition is excluded from the result set.
Filtering on individual data elements should be done with the WHERE clause.	Linking between two or more related tables should be done using an INNER JOIN ON clause
The WHERE clause syntax is more relational model-oriented.	SQL INNER JOIN is an ANSI syntax
the result of two tables JOIN’ed can be filtered on matching the columns when using the WHERE clause.	The INNER JOIN is generally considered more readable and it is a cartesian product of the tables, especially when you join lots of tables

Difference between INNER JOIN and OUTER JOIN

An INNER JOIN SQL is a type of join that returns all rows from both the participating tables, therefore, where the key record of the first table is equal to the key records of the second table. so, 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 related tables.

Whereas the OUTER JOIN SQL 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.

There are three types of OUTER JOIN SQL format:-

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

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

3: The FULL OUTER JOIN SQL, in 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.

SQL INNER JOIN: Table Aliases make SQL Joins Easier to Read

SQL Code:

Therefore, This type of join is called an Equi-Join, furthermore, since we are using equality for the join condition. so, it pretty safe to say that the vast majority of the joins you encounter are Equi-Joins. There are special circumstances where it makes sense to use other comparisons such as not equals or greater than. therefore, these types of joins are called non-Equi-joins. Once the data is joined it can also be filtered and sorted using familiar ORDER BY and WHERE clauses. The order of the clauses is what you might expect:

The WHERE clause refers to any of the fields from the joined tables. therefore, it is best practice to prefix the columns with the table name. The same goes for sorting. Use any field; however, it is common to sort by one or more related fields you are selecting. Let’s take our Laptop directory, but only list store, in order of the city, those whose city starts with D. therefore, With this requirement in your mind, our SQL statement becomes:

Important Notice: therefore, the join condition are remains the same but only changes are the addition of the WHERE and ORDER BY clauses. By now these should be familiar to you.