In SQL server what is the difference between using = and join?

Question

We have been learning SQL Server programming in Database Systems class. The professor goes exceptionally fast and is not very open to asking questions. I did ask him this, but he just advised me to review the code he'd given (which doesn't actually answer the question).

When making a query, what is the difference between using the term JOIN and using the "=" operator? For example, I have the following query:

SELECT VENDOR_NAME, ITEM_NAME, QTY
FROM   VENDOR, VENDOR_ORDER, INVENTORY
WHERE  VENDOR.VENDOR_ID = VENDOR_ORDER.VENDOR_ID
 AND  VENDOR_ORDER.INV_ID = INVENTORY.INV_ID
ORDER  BY VENDOR_NAME

In class the professor has used the following code:

SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME 
FROM CUSTOMER   JOIN INVOICE USING (CUS_CODE)
        JOIN LINE USING (INV_NUMBER) 
        JOIN PRODUCT USING (P_CODE)
WHERE P_DESCRIPT = 'Claw hammer';

It seems to me that using a join is performing the same function as the "=" is in mine? Am I correct or is there a difference that I am unaware of?

Edit: Trying to use Inner Join based on things I've found on Google. I ended up with the following.

SELECT VENDOR_NAME, ITEM_NAME, QTY
FROM   VENDOR, VENDOR_ORDER, INVENTORY
        INNER JOIN VENDOR_ORDER USING (VENDOR_ID)
        INNER JOIN INVENTORY USING (INV_ID)
ORDER  BY VENDOR_NAME

Now I get the error message ""VENDOR_ID" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90. " I'm using 2014, so my compatibility level is 120.

Answer 1

The difference between what you are doing (in your first example) and what your professor is doing is that you are creating a set of all possible combinations of the rows in those tables, then narrowing your results to the ones that match the way you want them to. He is creating a set of only the rows that match the way you want them to in the first place.

If your tables were:

Table1
ID1
1   
2    
3    

Table2
ID2
1    
2    
3    

Your query starts with basically a cross join:

Select * from Table1, Table2

ID1  ID2
1    1
2    1
3    1
1    2
2    2
3    2
1    3
2    3
3    3

Then narrows that result set down by applying the where ID1 = ID2

ID1  ID2
1    1
2    2
3    3

This is inefficient and somewhat difficult to read in more complex examples, as people have mentioned in the comments.

Your professor is building the criteria to relate the two tables into the join itself, so he is effectively skipping the first step. In our example tables, this would be Select * from Table1 join Table2 on ID1 = ID2.

There are several types of joins in SQL, which differ based on how you want to handle cases where a value exists in one of your tables, but has no match in the other table. See traditional venn diagram explanation from http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins:

Answer 2

Don't worry it's your professors issue not yours. Make sure you give appropriate feedback at the end of the course ;)

Hang in there.

So here is some info:

So the first issue is: your professor should not be teaching you USING because it has limited implementation (it definitely won't work in SQL Server) and IMHO it's a bad idea because you should explicitly list join columns.

Here are some queries that will work in SQL Server - lets build them up bit by bit. I will need to make some assumptions

First just join vendor to vendor order:

SELECT VENDOR.VENDOR_NAME, VENDOR_ORDER.QTY
FROM   VENDOR
INNER JOIN 
VENDOR_ORDER
ON VENDOR.VENDOR_ID = VENDOR_ORDER.VENDOR_ORDER

By using inner join we match these two tables on VENDOR_ID

If you have seven records in VENDOR_ORDER with VENDOR_ID = 7, and one record in table VENDOR then the result of this will be.... 7 records, with the data from the VENDOR table repeating seven times.

Now to that, join in inventory

SELECT VENDOR.VENDOR_NAME, INVENTORY.ITEM_NAME, VENDOR_ORDER.QTY
FROM   VENDOR
INNER JOIN 
VENDOR_ORDER
ON VENDOR.VENDOR_ID = VENDOR_ORDER.VENDOR_ORDER
INNER JOIN
INVENTORY ON INVENTORY.INV_ID = VENDOR_ORDER.INV_ID
ORDER  BY VENDOR.VENDOR_NAME

This 'INNER JOIN' syntax is the modern version (often referred as SQL-92). Having a comma seperated list after the FROM clause is 'old school'

Both methods work the same way but the old school way causes ambiguities if you start using outer joins. So get into the habit of doing it the new way.

Lastly, to neaten things up you can use an 'allias'. Which means you give each table a shorter name then use that. I've also added in the invoice number so you can get an idea of what's going on:

SELECT V.VENDOR_NAME, I.ITEM_NAME, ORD.INV_ID, ORD.QTY
FROM   VENDOR As V
INNER JOIN 
VENDOR_ORDER As ORD
ON V.VENDOR_ID = ORD.VENDOR_ORDER
INNER JOIN
INVENTORY As I ON I.INV_ID = ORD.INV_ID
ORDER  BY V.VENDOR_NAME