Using AND in an INNER JOIN

Question

I am fairly new with SQL would like to understand the logic below.

SELECT *
FROM Table A A1 
INNER JOIN TABLE B B1 ON B1.ID = A1.ID AND A1 = 'TASK';

Not sure if this is a clear detail but please let me know. Thanks!

Answer 1

SELECT *
FROM Table A A1
    INNER JOIN TABLE B B1 ON B1.ID = A1.ID AND A1.Column = 'TASK'

is the same as

SELECT *
FROM Table A A1
    INNER JOIN TABLE B B1 ON B1.ID = A1.ID
WHERE A1.Column = 'TASK'

It's even the same performance wise, it's just a different way to write the query. In very large queries it can be more readable to use an AND directly on an INNER JOIN instead of "hiding" it the in the WHERE part.

Answer 2

This wouldn't run at all

SELECT *
FROM Table A A1 INNER JOIN
     TABLE B B1 
     ON B1.ID = A1.ID AND A1 = 'TASK';

This will run because I added a column name (SomeColumn):

SELECT *
FROM Table A A1 INNER JOIN
     TABLE B B1 
     ON B1.ID = A1.ID AND A1.SomeColumn = 'TASK';

And is the same as this

SELECT *
FROM Table A A1 INNER JOIN
     TABLE B B1 
     ON B1.ID = A1.ID 
WHERE A1.SomeCoumn = 'TASK';

Whenever you join to a constant it is pretty much the same as adding an additional criterion to the where clause. The only reason to put it up with the join is for code clarity.