How do these two queries return the same result but use different joins? (Implicit vs. Explicit Query)

Question

I have 2 tables: Order and Customer. Order has a foreign key from Customer:

I can join them in 2 different ways:

First way

Select *
from [Order] 
join Customer
on [Order].Customer_id = Customer.id;

Second way

Select * 
from [Order],Customer
where [Order].Customer_id = Customer.id;

The 2 queries return the same result set which leads me to my related questions:

Which query is the better of the two?
Is there a difference between them involving time execution?
Why is it that when I search join examples all of them are using the first way?
I learned the second type of query in college - is it wrong to use?

Well your first query is called explicit join and your second query is called implicit join. You should always use explicit join for best practices. — zealous, May 22 '20 at 04:44
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, May 22 '20 at 04:59
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 22 '20 at 05:00

score 0 · Accepted Answer · answered May 22 '20 at 04:50

The difference occurs if you are using LEFT OUTER JOIN or RIGHT OUTER JOIN. filtration location matters here because criteria specified in the ON clause is applied before the JOIN is made. Criteria against an OUTER JOINed table provided in the WHERE clause is applied after the JOIN is made. This can produce very different result sets.

In comparison, it doesn't matter for INNER JOINs if the criteria is provided in the ON or WHERE clauses, the result will be the same.

How do these two queries return the same result but use different joins? (Implicit vs. Explicit Query)

1 Answers1