The INNER join is one of the most commonly used join types in SQL Server. However, many people are not certain about how it produces the result set when the join condition is changed. In this article, we will delve into the details of INNER join and explore how it works.
Before we begin, let’s create two tables to illustrate the examples used in this article:
CREATE TABLE #InnerJoinTest1 ( InnerJoinTest1_PK INT IDENTITY(1,1) ) CREATE TABLE #InnerJoinTest2 ( InnerJoinTest2_PK int )
Now, let’s populate these tables with some sample data:
-- Populate table 1 WHILE SCOPE_IDENTITY() < 5 OR SCOPE_IDENTITY() IS NULL INSERT INTO #InnerJoinTest1 DEFAULT VALUES -- Populate table 2 INSERT INTO #InnerJoinTest2 SELECT * FROM #InnerJoinTest1
With the tables and data set up, we can now explore different scenarios using INNER join. Let’s take a look at some example queries:
-- Query 1 SELECT a.*, b.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK = b.InnerJoinTest2_PK -- Query 2 SELECT a.*, b.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK > b.InnerJoinTest2_PK -- Query 3 SELECT a.*, b.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK >= b.InnerJoinTest2_PK -- Query 4 SELECT b.*, a.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK < b.InnerJoinTest2_PK ORDER BY a.InnerJoinTest1_PK -- Query 5 SELECT b.*, a.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK <= b.InnerJoinTest2_PK ORDER BY a.InnerJoinTest1_PK -- Query 6 SELECT a.*, b.* FROM #InnerJoinTest1 a INNER JOIN #InnerJoinTest2 b ON a.InnerJoinTest1_PK <> b.InnerJoinTest2_PK
Now, let’s understand how the result set is produced for each query:
Cartesian Product
First, let’s consider table 1 as set 1 and table 2 as set 2. The Cartesian product of these two sets is the maximum possible number of combinations that can be created from the given sets. In our case, the number of records in the Cartesian product is 25 (5×5).
Rationalization
Now, let’s analyze each query and determine the result set based on the Cartesian product:
Query 1: This INNER JOIN with the equal condition will give you the matching records of both sets. The result set will be the highlighted records in the Cartesian product.
Query 2: This query will return all possible record combinations where the value in the first table is greater than the value in the second table. Compare the result set with the highlighted records in the Cartesian product.
Query 3: This query will produce the records where InnerJoinTest1_PK is greater than or equal to InnerJoinTest2_PK. Again, refer to the Cartesian product to determine the result set.
Query 4 & 5: Query 4 will produce the records where InnerJoinTest1_PK is less than InnerJoinTest2_PK, while Query 5 will include the equal values of both columns in addition to the output of Query 4. The order of results from the actual execution of these queries may differ from the Cartesian product, but you can use an ORDER BY clause to order the result set as desired.
Query 6: This query will output the records where both columns are not equal. Refer to the Cartesian product to understand the result set.
Conclusion
By understanding the different types of INNER JOIN and how the result set is determined without actually executing the query, you will have a better understanding of SQL Server development and administration work. INNER join is a powerful tool for combining data from multiple tables, and knowing how it works will help you write more efficient and effective queries.
Thank you for reading this article. If you have any further questions or would like to discuss this topic further, please feel free to leave a comment.