Menu
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else:
SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey WHERE (some other conditions) Do these two work on the same way in MySQL?
862
If you want to use a JOIN other than an INNER JOIN stating it explicitly makes it clear what is going on. JOINing in the WHERE clause can be confusion since this is not it's typical purpose. It is most often used to filter the data.
The subquery can be placed in the following SQL clauses they are WHERE clause, HAVING clause, FROM clause. Advantages Of Joins: The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
Actually you often need both "WHERE" and "JOIN". "JOIN" is used to retrieve data from two tables - based ON the values of a common column. If you then want to further filter this result, use the WHERE clause. For example, "LEFT JOIN" retrieves ALL rows from the left table, plus the matching rows from the right table.
INNER JOIN is ANSI syntax that you should use.
It is generally considered more readable, especially when you join lots of tables.
It can also be easily replaced with an OUTER JOIN whenever a need arises.
The WHERE syntax is more relational model oriented.
A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.
It's easier to see this with the WHERE syntax.
As for your example, in MySQL (and in SQL generally) these two queries are synonyms.
Also, note that MySQL also has a STRAIGHT_JOIN clause.
Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.
You cannot control this in MySQL using WHERE syntax.
119
Others have pointed out that INNER JOIN helps human readability, and that's a top priority, I agree.
Let me try to explain why the join syntax is more readable.
A basic SELECT query is this:
SELECT stuff FROM tables WHERE conditions The SELECT clause tells us what we're getting back; the FROM clause tells us where we're getting it from, and the WHERE clause tells us which ones we're getting.
JOIN is a statement about the tables, how they are bound together (conceptually, actually, into a single table).
Any query elements that control the tables - where we're getting stuff from - semantically belong to the FROM clause (and of course, that's where JOIN elements go). Putting joining-elements into the WHERE clause conflates the which and the where-from, that's why the JOIN syntax is preferred.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
