SQL Inner Join

SQL Inner Join

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.

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 in both the students and courses tables. [Source]

SQL Inner Joins Example:

The "Consumers" table:

P_Id	LastName	FirstName	Address	City
1	Kumar	Ram	Delhi	AAA
2	Singh	Laxman	Chandigarh	AAA
3	Sharma	Sameer	Ambala	BBB

The "Orders" table:

O_Id	OrderNo	P_Id
1	12355	3
2	12356	3
3	12357	1
4	24562	1
5	34764	15

Now we want to list all the Consumers with any orders.

We use the following SELECT statement:

SELECT Consumers.LastName, Consumers.FirstName, Orders.OrderNo
FROM Consumers
INNER JOIN Orders
ON Consumers.P_Id=Orders.P_Id
ORDER BY Consumers.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Kumar	Ram	12357
Kumar	Ram	24562
Sharma	Sameer	12355
Sharma	Sameer	12356

Atleast one match should be there in both tables involved in inner join in order for the query to return the rows.

When to useinner join

Use an inner join when you want to match values from both tables.

Why to use Inner Joins:

Use inner joins to obtain information from two separate tables and combine that information in one result set.

When you use inner joins, consider the following facts and guidelines:

1. Inner joins are the SQL Server default. You can abbreviate the INNER JOIN clause to JOIN.

2. Specify the columns that you want to display in your result set by including the qualified column names in the select list.

3. Include a WHERE clause to restrict the rows that are returned in the result set.

4. Do not use a null value as a join condition because null values do not evaluate equally with one another.

5. SQL Server does not guarantee an order in the result set unless one is specified with an ORDER BY clause. [Source: Microsoft SQL Server 7.0 data warehousing training]