RSS Feed

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_IdLastName FirstNameAddress City
1KumarRamDelhi AAA
2Singh LaxmanChandigarh AAA
3SharmaSameerAmbalaBBB

The "Orders" table:

O_IdOrderNoP_Id
1123553
2123563
3123571
4245621
53476415

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 FirstNameOrderNo
KumarRam12357
KumarRam24562
SharmaSameer12355
SharmaSameer12356

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]