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;