SQL INNER JOIN over multiple tables equal to WHERE syntax
The query planner takes all WHERE
and JOIN
predicates into consideration in (almost) the same way, when trying to optimize join order. It's no surprise you get the same performance. Per documentation:
Explicit inner join syntax (
INNER JOIN
,CROSS JOIN
, or unadornedJOIN
)
is semantically the same as listing the input relations inFROM
, so it
does not constrain the join order.
The only difference: explicit join syntax instructs the given order of joins when the total number of tables is greater than the setting for join_collapse_limit
.
And explicit JOIN
s bind before commas in the FROM
list, which is relevant to permissible syntax. Example.
In any case, comma-separated lists of tables in the FROM
clause are in no way deprecated. It's just good form and much more readable to use explicit join syntax.
Also note that OUTER
joins behave differently in that they cannot be rearranged as freely by the query planner due to logic implications.
SQL Inner join more than two tables
SELECT *
FROM table1
INNER JOIN table2
ON table1.primaryKey=table2.table1Id
INNER JOIN table3
ON table1.primaryKey=table3.table1Id
INNER JOIN vs multiple table names in FROM
There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it doesn't work correctly right now anyway) differs from vendor to vendor. Since you shouldn't mix implicit and explict joins in the same query (you can get wrong results), needing to change something to a left join means rewriting the entire query.
INNER JOIN ON vs WHERE clause
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 JOIN
ed 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.
SQL Inner join 2 tables with multiple column conditions and update
UPDATE
T1
SET
T1.Inci = T2.Inci
FROM
T1
INNER JOIN
T2
ON
T1.Brands = T2.Brands
AND
T1.Category= T2.Category
AND
T1.Date = T2.Date
Joining two tables where id does not equal
SELECT * FROM table1
INNER JOIN table2 ON table2.empid = table1.empid AND table2.alt_id <> table1.alt_id
How to join two tables by multiple columns in SQL?
Yes: You can use Inner Join
to join on multiple columns.
SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND
E.FileNum = V.FileNum AND
E.ActivityNum = V.ActivityNum
Create table
CREATE TABLE MyNewTab(CaseNum int, FileNum int,
ActivityNum int, Grade int, Score varchar(100))
Insert values
INSERT INTO MyNewTab Values(CaseNum, FileNum, ActivityNum, Grade, Score)
SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND
E.FileNum = V.FileNum AND
E.ActivityNum = V.ActivityNum
How to do an INNER JOIN on multiple columns
You can JOIN with the same table more than once by giving the joined tables an alias, as in the following example:
SELECT
airline, flt_no, fairport, tairport, depart, arrive, fare
FROM
flights
INNER JOIN
airports from_port ON (from_port.code = flights.fairport)
INNER JOIN
airports to_port ON (to_port.code = flights.tairport)
WHERE
from_port.code = '?' OR to_port.code = '?' OR airports.city='?'
Note that the to_port
and from_port
are aliases for the first and second copies of the airports
table.
Related Topics
How to Select Records That Don't Exist in SQL Server
How to Pass a Variable That Contains a List to a Dynamic SQL Query
To Get Date from Datetime in SQL
Using Regular Expression Within a Stored Procedure
Error Converting Data Type Varchar
How to Compare If Two Strings Contain the Same Words in T-SQL for SQL Server 2008
Sql/Postgresql Left Join Ignores "On = Constant" Predicate, on Left Table
Bulk Update Multiple Rows in Same Query Using Postgresql
Aggregate String Concatenation in Oracle 10G
SQL Select for All Records That May Holds Specific Value
Displaying Row Numbers Column at Runtime
Querying and Selecting Specific Column in SQLalchemy
How to Preserve the Order of a SQL Query Using the in Command
Insert Identity Column Value into Table from Another Table
How to Pass Column Name as Parameter in Select Statement SQL Server