Questions tagged [on-clause]
34 questions
930
votes
22 answers
SQL JOIN - WHERE clause vs. ON clause
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins.
The answer may be related (or even the same) but the question is different.
What is the difference and what should go in each?
If I understand the theory correctly, the…
BCS
- 75,627
- 68
- 187
- 294
29
votes
6 answers
What's the difference between "where" clause and "on" clause when table left join?
SQL1:
select t1.f1,t2.f2
from t1
left join t2 on t1.f1 = t2.f2 and t1.f2=1 and t1.f3=0
SQL2:
select t1.f1,t2.f2
from t1
left join t2 on t1.f1 = t2.f2
where t1.f2=1 and t1.f3=0
The difference is where and on clause, is it returning same…
SleeplessKnight
- 2,125
- 4
- 20
- 28
8
votes
3 answers
How do constant values effect the ON clause of Joins?
I've recently discovered that the ON clause of a LEFT JOIN may contain values such as (1 = 1).
This is upsetting to me, as it breaks my perception of how joins function.
I've encountered a more elaborate version of the following situation:
SELECT…
Jude Allred
- 10,977
- 7
- 28
- 27
4
votes
2 answers
Where is better to put 'on' conditions in multiple joins? (mysql)
I have multiple joins including left joins in mysql. There are two ways to do that.
I can put "ON" conditions right after each join:
select * from A join B ON(A.bid=B.ID) join C ON(B.cid=C.ID) join D ON(c.did=D.ID)
I can put them all in one…
morteza kavakebi
- 1,640
- 3
- 18
- 39
3
votes
1 answer
MySQL won't use available indexes in JOIN if OR is used in ON clause
Say you have 5 tables, with each their columns:
house (id, name, street_id)
street (id, name)
photo (id, name)
house_photo (house_id, photo_id)
street_photo (street_id, photo_id)
And say all 'id' columns and column ending with '_id' already have…
nl-x
- 11,762
- 7
- 33
- 61
3
votes
1 answer
SQL JOIN in PostgreSQL - Different execution plan in WHERE clause than in ON clause
We have a simple statement in PostgreSQL 11.9/11.10 or 12.5 where we can write the join with a WHERE-CLAUSE or with a ON-CLAUSE. The meaning is exactly the same and therefore the number of returned rows too - But we receive a different explain plan.…
Sandro Schifferle
- 41
- 4
3
votes
1 answer
Predicate Pushdown vs On Clause
When performing a join in Hive and then filtering the output with a where clause, the Hive compiler will try to filter data before the tables are joined. This is known as predicate pushdown…
vi_ral
- 369
- 4
- 19
3
votes
1 answer
SQLAlchemy Conjunction with Parentheses
I am trying to produce the below sql in sqlalchemy core. I am not able to get the parentheses in the conjunction AND OR to appear.
select from member t1
inner join member t2
on (
(
t1.first_name = t2.last_name and
…
user1223862
- 1,193
- 2
- 9
- 10
2
votes
3 answers
Using Aliases in the ON Clause of a JOIN
New to Stack Overflow (and coding in general).
I did some research but was unable to find an answer to the following problem:
How can I join two tables ON the results of functions applied to dimensions, rather than on the dimensions themselves?
i.e.…
Ben Leathers
- 184
- 1
- 2
- 9
1
vote
1 answer
INSERT INTO with subquery and ON CONFLICT
I want to insert all elements from a JSON array into a table:
INSERT INTO local_config(parameter, value)
SELECT json_extract(j.value, '$.parameter'), json_extract(j.value, '$.value')
FROM json_each(json('[{"parameter": 1, "value": "value1"},…
Ralph Bergmann
- 3,015
- 4
- 30
- 61
1
vote
1 answer
subquerying in WHERE/Joining 3 tables, 2 for records and one for number, returns no result/fails - MSAccess
My query does return any records. Depending on how I write it, it returns no records or all records, although I don't have the code that just returned everything.
I need to pull data from two sources with actual records, and a third table which has…
Chris Oyer
- 13
- 3
1
vote
3 answers
MYSQL FULL OUTER JOIN - All NULL results when using LEFT-UNION-LEFT JOIN
Tbl_A
cap_id| yr_a| sp_a| iso_a| area_a| qty_a | prod_a |
3| 2015| TRR| 54| 8| 120 | 0 |
678| 2015| BOM| 62| 27| 0.0 | 0 |
20| 2015| TRR| 54| 27| 0.0 | 0 |
45| 2015| FRC| 7| 15|…
sq_peg_rnd_hole
- 73
- 1
- 7
1
vote
1 answer
Is an "on" criteria quicker than a "where"?
Let us suppose we have a join between t1 and t2, like this:
select c1, ..., cn
from t1 join t2
on t1.fk = t2.k
join t3
on t2.fk = t3.k
where (some condition independent from t3)
where (some condition independent from t3) might be anything. Now,…
Lajos Arpad
- 64,414
- 37
- 100
- 175
1
vote
3 answers
MySQL placement of conditions in on-clauses of multiple outer joins
As a follow up to In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement? and SQL join: where clause vs. on clause - it does matter if a condition is placed in the on-clause vs. the where-clause in an outer…
Arnon Weinberg
- 871
- 8
- 20
1
vote
1 answer
Right outer join on two tables where ON clause has a subquery
I am trying to perform a right outer join on two liferay tables — users_ and expandovalue — to get a result set.
When I did the following query on all the users, I got desired result.
SELECT USER_.FIRSTNAME
, USER_.LASTNAME
,…
user1666444
- 21
- 3