Search code examples
sql-serverjoinequals-operator

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

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.

Solution

  • 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: Traditional Venn Diagram