CODEWITHSUNDEEP
sqlinner-join
sonnymoon
sonnymoon

Reputation: 153

Using AND in an INNER JOIN

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!

Upvotes: 15

Views: 46141

Answers (3)

John Wu
John Wu

Reputation: 52260

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.

Upvotes: 6

waka
waka

Reputation: 3417

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.

Upvotes: 16

speedinfusion
speedinfusion

Reputation: 385

SELECT * -- Select all the columns

FROM TABLE A A1 -- From the table A. A1 is like a nickname you are giving table A. Instead of typing A.ColumnName (A couldbe a very long name) you just type A1.ColumnName

INNER JOIN TABLE B B1 -- You are inner joining Table A and B. Again, B1 is just a nickname. Here is a good picture explaning joins.enter image description here

ON B1.ID = A1.ID -- This is the column that the 2 tables have in common (the relationship column) These need to contain the same data.

AND A1 = 'TASK' -- This is saying you are joining where A1 tablename

Upvotes: 4

Related Questions