July 31, 2018 at 12:13 pm
Hello,
I have taken over a departured colleague's work, one of the script she left over is to find out the gap between two tables: chn01 and cl01, here is one query she wrote:
Find out the stats for chn01 and not in cl01, group/order by date_pull
select chn.date_pull,count(distinct chn.id_cust) dist_custs
from Citz0063.v201806_chn01 chn
where not exists (select 1 from Citz0063.v201806_cl01 cust where chn.date_pull=cust.date_pull and chn.id_cust=cust.id_cust)
group by chn.date_pull
order by chn.date_pull desc;
I don't understand why she is using select 1 in the where not exists
Can anyone tell me if this query is correct? if not, how can it be revisited?
Thank you very much.
July 31, 2018 at 12:22 pm
PasLe Choix - Tuesday, July 31, 2018 12:13 PMHello,I have taken over a departured colleague's work, one of the script she left over is to find out the gap between two tables: chn01 and cl01, here is one query she wrote:
Find out the stats for chn01 and not in cl01, group/order by date_pull
select chn.date_pull,count(distinct chn.id_cust) dist_custs
from Citz0063.v201806_chn01 chn
where not exists (select 1 from Citz0063.v201806_cl01 cust where chn.date_pull=cust.date_pull and chn.id_cust=cust.id_cust)
group by chn.date_pull
order by chn.date_pull desc;I don't understand why she is using select 1 in the where not exists
Can anyone tell me if this query is correct? if not, how can it be revisited?
Thank you very much.
It looks fine. The EXISTS () part performs a check on existence of data, as per the SELECT query which appears in the brackets, and does not select any data at all.
In words:
select columns from table Citz0063.v201806_chn01, where no matching rows are found in table Citz0063.v201806_cl01. Matching is performed on the date_pull and id_cust columns only.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2018 at 12:32 pm
PasLe Choix - Tuesday, July 31, 2018 12:13 PMHello,I have taken over a departured colleague's work, one of the script she left over is to find out the gap between two tables: chn01 and cl01, here is one query she wrote:
Find out the stats for chn01 and not in cl01, group/order by date_pull
select chn.date_pull,count(distinct chn.id_cust) dist_custs
from Citz0063.v201806_chn01 chn
where not exists (select 1 from Citz0063.v201806_cl01 cust where chn.date_pull=cust.date_pull and chn.id_cust=cust.id_cust)
group by chn.date_pull
order by chn.date_pull desc;I don't understand why she is using select 1 in the where not exists
Can anyone tell me if this query is correct? if not, how can it be revisited?
Thank you very much.
The SELECT clause is ignored in an EXISTS/NOT EXISTS subquery. Some people have started to use "SELECT 1" to indicate that it's not returning any fields from the table. I slightly prefer "SELECT *" myself.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 31, 2018 at 12:50 pm
drew.allen - Tuesday, July 31, 2018 12:32 PMThe SELECT clause is ignored in an EXISTS/NOT EXISTS subquery.
This example reinforces the above:
SELECT 1
WHERE EXISTS (SELECT 1/0)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply