November 16, 2016 at 2:13 pm
Hello,
What are the effects of using an additional join condition in an INNER JOIN clause? For Example:
SELECT *
FROM LAB_HEAD AS A
INNER JOIN LAB_DET AS B
ON A.ID = B.HEAD_ID
AND A.OrderNum = B.OrderNum
How would the results differ if I removed "AND A.OrderNum = B.OrderNum" from the Join Clause? If I have not provided enough information, please let me know.
Thank you for your time
EDIT: I had originally assumed there would be no difference since it's an inner join, but I ran a real query both ways and got a vastly different number of rows.
November 16, 2016 at 2:35 pm
When using AND in a JOIN, it usually means that you have a composite key which would consist on 2 or more columns.
As you have realized, removing one or more columns from the JOIN criteria would return incorrect results. This is because the key is incomplete.
Imagine that you're asked to pick all boys with age 10 from a school database. You need both conditions to ensure that you get the group correctly. Otherwise, you might find yourself with boys with different ages.
Here's a simple example to show the differences.
CREATE TABLE #Table1(
ID int,
OrderNum int);
INSERT INTO #Table1
VALUES(1,1), (1,2);
SELECT * FROM #Table1;
CREATE TABLE #Table2(
ID int,
OrderNum int);
INSERT INTO #Table2
VALUES(1,1), (1,3);
SELECT * FROM #Table2;
SELECT *
FROM #Table1 AS A
INNER JOIN #Table2 AS B ON A.ID = B.ID
AND A.OrderNum = B.OrderNum;
SELECT *
FROM #Table1 AS A
INNER JOIN #Table2 AS B ON A.ID = B.ID;
DROP TABLE #Table1;
DROP TABLE #Table2;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply