Questions tagged [on-clause]
The on-clause tag has no usage guidance.
35
questions
962
votes
22
answers
1.0m
views
SQL JOIN: what is the difference between WHERE clause and ON clause?
What is the difference and what should go in each?
If I understand the theory correctly, the query optimizer should be able to use both interchangeably.
(Note: this question is not a duplicate of ...
- 77k
29
votes
6
answers
32k
views
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 ...
- 2,145
8
votes
3
answers
19k
views
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 ...
- 11k
4
votes
2
answers
2k
views
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) ...
- 1,650
3
votes
1
answer
3k
views
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 (http://...
- 369
3
votes
1
answer
387
views
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' ...
- 11.8k
3
votes
1
answer
1k
views
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 (
(
...
- 1,193
3
votes
1
answer
1k
views
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 ...
2
votes
3
answers
4k
views
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 ...
- 324
2
votes
0
answers
63
views
bad performance of postgres query if on-clause of inner-join is positioned after following left-joins
Background: Hibernate 6 creates some generated queries different than hibernate 5.
In hibernate 5 a query looked like this (simplified):
select *
from document
inner join row on document.id = ...
- 43
1
vote
1
answer
1k
views
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(...
- 3,046
1
vote
3
answers
428
views
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 ...
- 881
1
vote
1
answer
372
views
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.
...
- 21
1
vote
1
answer
85
views
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 ...
- 13
1
vote
3
answers
185
views
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| ...
1
vote
1
answer
113
views
Is an "on" criteria quicker than a "where"? [duplicate]
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 ...
- 70.2k
0
votes
2
answers
803
views
Null result from multiple left Join in postgresql
I have 3 tables in a database A, B and C which share the same column "name".
B has an attribute "title" and C has an attribute "age".
I am looking to write a SQL query ...
- 171
0
votes
1
answer
7k
views
Teradata - Adding preceding zero and casting as varchar(50)
I have the following query:
SELECT
s.cola, s.colb, t.colc, t.cold, u.cole, u.colf, u.colg, u.colh, u.coli, u.colj, u.colk, u.coll
FROM table1 s
INNER JOIN table2 t
ON s.colb = t.colc
INNER JOIN ...
- 934
0
votes
2
answers
113
views
Why doesn't this work with an ON clause, but does with a WHERE clause?
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for ...
- 15
0
votes
1
answer
68
views
How can I remove subquery?
I need to remove the subquery. But I must maintain the condition. What can I do?
(SELECT * FROM customer_orders where status=3)
SELECT cus.id,cus.customer_name,cus.mobile,cus.email,
COUNT(CAST(...
- 217
0
votes
1
answer
833
views
MySQL join two tables based on a condition
I have two tables.
users
| id | name |
| ---|-------|
| 1 | Jhon |
| 2 | David |
swipes
| id | swp_from | swp_to| first_swp| second_swp |
| ---|----------|-------|----------|------------|
| 1 | ...
- 462
0
votes
2
answers
490
views
Error Number: 1054 Unknown column '2021-08-23' in 'on clause'
I have join many tables in this query via LEFT JOIN and i want to apply condition on table but php is giving me following error
Error Number: 1054 Unknown column '2021-08-23' in 'on clause'
SELECT i....
- 17
0
votes
1
answer
792
views
CI and in the join on
$this->default->join('db D', 'C.col1 = D.col1 AND D.col2 = "MAIN"', 'LEFT');
I am getting 500 error on this join in CI but when i only use
$this->default->join('db D', 'C.col1 = D.col1',...
- 15.5k
0
votes
1
answer
1k
views
Oracle MERGE: only NOT MATCHED is triggered
Database: Oracle
Table:
CREATE TABLE TABLE_FOR_TESTS (
d DATE,
t NUMBER(8)
)
MERGE:
MERGE INTO TABLE_FOR_TESTS
USING DUAL
ON ((SELECT COUNT(*) FROM TABLE_FOR_TESTS) = 1)
WHEN ...
- 55.6k
0
votes
2
answers
2k
views
Any possible way to add parameters to ON clause on include left joins on rails?
I have a huge complex query like this:
@objects = Object.joins({ x: :y }).includes(
[:s, { x: { y: :z } }, { l: :m },:q, :w,
{ important_thing:
[:h, :v, :c,:l, :b, { :k [:u, :a] }]
...
- 1,220
0
votes
2
answers
5k
views
Why subquery doesn't work in ON clause in DB2
Why this simple query works fine in oracle but doesn't work in DB2:
select *
from
sysibm.dual d1
left join sysibm.dual d2 on 1=1 and exists (select 1 from sysibm.dual)
Moving subquery-involving ...
0
votes
1
answer
1k
views
Multiple on clause using codeigniter active record
I need to do a multiple on clause on a left join query using codeigniter active record.
I write this code:
$this->db->join('table1', 'table1.col1 = table2.id', 'left');
$this->db->...
- 2,110
0
votes
1
answer
113
views
Linq left joining with non trivial condition
This is fine, it produces a left join
var q =
from c in categories
join p in products
on c equals p.Category into ps
from p in ps.DefaultIfEmpty()
select new { Category = c, ...
- 2,971
0
votes
2
answers
216
views
SQL Left Join with WHERE clause SAS Enterprise Guide
I am trying to pair 2 tables (nicknamed PERSIP and ECIF) on their ID field, (labeled TABLE1 & TABLE2) to create a RESULTTABLE, where the ym_id (for both tables) variable is set to my timekey0 ...
- 3
0
votes
0
answers
103
views
HQL query for Collection using ON clause
Below is the HQL query which gives me the syntax error when I use join Operation with ON clause on the Collection property.
I have a Source.java file with one Collection property of the Customer bean ...
- 23
0
votes
1
answer
32
views
MYSQL adding OR clause in left join slowing up the query
I have two database tables: orders and customers.
I'm running a SQL to get all orders in June month.
If the Ship To and Bill To email are different, we are inserting two different records with ...
- 8,370
0
votes
1
answer
70
views
Sqlite ON statement on calculated field failing
I'm trying the following query with no luck:
SELECT B.id AS BookId, P.id AS PdfId FROM tbl_Books AS B
INNER JOIN tbl_PDF AS P ON P.FileName = B.ShortName || '.pdf'
COLLATE NOCASE
Is in Sqlite ...
- 936
0
votes
2
answers
63
views
I am trying to link 4 tables together with INNER JOIN
My code looks like this
CREATE TABLE Genre (
genreID INT NOT NULL DEFAULT 0,
genreName VARCHAR(20) NULL,
PRIMARY KEY (genreID));
CREATE TABLE Artists (
ArtistID INT NOT NULL DEFAULT 0,
name VARCHAR(...
user3487927
0
votes
1
answer
203
views
Customize JPA CriteriaQuery's on-clause
Is there a way to further restrict a join by adding some expressions? With plain sql i write:
SELECT c.*, COUNT(i.id) invoice_count
FROM customers c
LEFT JOIN invoices i ON i.customer_id = c.id
...
- 1,305
0
votes
1
answer
1k
views
Doctrine 2 and "unknown column in "ON clause"
I am currently working in a project that is using Doctrine 2 with ZF. So far so good. However I have a problem that looks like a bug.
I have written the following code in one of my repository :
$dql ...
- 1