89
votes
Accepted
Best practice between using LEFT JOIN or NOT EXISTS
The biggest difference is not in the join vs not exists, it is (as written), the SELECT *.
On the first example, you get all columns from both A and B, whereas in the second example, you get only ...
- 69.5k
37
votes
Best practice between using LEFT JOIN or NOT EXISTS
Logically they are identical, but NOT EXISTS is closer to the AntiSemiJoin that you're asking for, and is generally preferred. It also highlights better that you can't access the columns in B, because ...
- 16.2k
37
votes
Accepted
IF EXISTS taking longer than embedded select statement
Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.
As I ...
- 82.1k
25
votes
IF EXISTS taking longer than embedded select statement
Because EXISTS only needs to find a single row, it will use a row goal of one. This can produce a less-than-ideal plan sometimes. If you expect it will be like that for you, populate a variable with ...
- 16.2k
21
votes
Accepted
Wrapping query in IF EXISTS makes it very slow
As has been explained by Paul White in his blog post: Inside the Optimizer: Row Goals In Depth the EXISTS introduces a row goal, which prefers NESTED LOOPS or MERGE JOIN over HASH MATCH
As a final ...
- 15.6k
12
votes
Accepted
How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?
How does SQL Server know which rows to delete?
To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end.
First, let's ...
11
votes
Accepted
Does the SQL Spec require a GROUP BY in EXISTS ()
I found it in the SQL 2011 spec...
If the <select list> “*” is simply contained in a <table subquery> that is immediately contained in an <exists predicate>, then the <select ...
- 61.4k
10
votes
Accepted
pg_restore ERROR: "Relation does not exist" and creating new database
When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it'...
- 30.4k
9
votes
Best practice between using LEFT JOIN or NOT EXISTS
An exception I've noticed to the NOT EXISTS being superior (however marginally) to LEFT JOIN ... WHERE IS NULL is when using Linked Servers.
From examining the execution plans, it appears that NOT ...
- 430
9
votes
Accepted
EXISTS() vs EXISTS() = TRUE in Postgres
PostgreSQL is able to optimize WHERE EXISTS (/* correlated subquery */) into a join or semi-join, but it is not smart enough to detect that the = TRUE in EXISTS (...) = TRUE can be removed, so it does ...
- 45.6k
8
votes
Best practice between using LEFT JOIN or NOT EXISTS
In general, the engine will create an execution plan based essentially on:
The number of rows in A and B
Whether there is an Index on A and/or B.
The expected number of result rows (and intermediate ...
- 2,076
4
votes
Accepted
Howto combine UNION with EXISTS?
The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this:
SELECT PubKey, Title FROM Publication
WHERE EXISTS (
SELECT *
...
- 13k
4
votes
Accepted
How to determine if a timestamp in one table is between two timestamps in a subquery of another table?
You can do this with one EXISTS subquery:
UPDATE "index" AS i
SET pln = TRUE
WHERE EXISTS
( SELECT *
FROM planning AS p
WHERE p.state = 'selected'
AND p.macro NOT ...
- 96.8k
4
votes
Accepted
Select all from A where attribute exists in B
The (somewhat trivial) answer seems to be:
Cars ⋉ Ford
Relational algebra, semijoin (Wikipedia)
Alternatively (in response to a comment):
πR(R ⋈ S)
- 82.1k
4
votes
Accepted
How to check if all elements of an array exists in a table?
First count the number of distinct numbers. You have called it n. Then
SELECT COUNT(*) FROM tbl
WHERE id IN (very-long-list)
See if that returns n.
If, instead, you have put those numbers in ...
- 76.2k
4
votes
Which Plan is Better? WHERE EXISTS Instead of INNER JOIN
Here are stats of two plans:
Old plan: You've got a 1,864 memory grant, estimated number of rows 400
New plan: You've got a 69,056 memory grant, estimated number of rows 43,017
So we have two ...
- 1,999
4
votes
Accepted
NOT EXISTS with two subquery fields that match 2 fields in main query
You are not using EXISTS correctly. In an EXISTS, the selected column makes no difference, it is entirely ignored and does not even need a name. This is why I favour the syntax EXISTS (SELECT 1 all on ...
- 11.4k
3
votes
How to check if all elements of an array exists in a table?
CREATE TEMPORARY TABLE B_Temp (
id BIGINT NOT NULL
PRIMARY KEY (id)
INDEX unid (id)
);
I would load a temp table as in @AMG 's answer. But my query would look like this:
select id
...
- 186
3
votes
How to check if all elements of an array exists in a table?
I recommend to use the application code to insert the array into a Temporary table, including an identity field so you can store every record, even if it is repeated. (the syntaxis is the same as ...
- 1,342
3
votes
Accepted
Why my query does not return correct data (NORTHWIND)?
There are two orders with an orderdate=1997-09-05. One for employee=4 and one for employee=7. Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the ...
- 23.7k
3
votes
How does 'exists' affect output
According to the Microsoft Docs page for the EXISTS operator:
EXISTS returns TRUE if a subquery contains any rows.
The PostgreSQL documentation page says this about the exists function:
The ...
- 69.5k
3
votes
Accepted
Help filtering out records in MYSQL
SELECT koalaName
FROM main
GROUP BY koalaName
HAVING SUM( fate IN ('Released', 'Relocated', 'Care') ) > 0
AND SUM( fate = 'Found Dead') = 0
- 19.2k
3
votes
Optimise XOR search on int list
Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?
although I'm thinking ...
- 45.3k
3
votes
Return all archive records when any one record meets criteria
The WHERE clause in the subquery is not correct:
the condition should be checking the subquery table (PTAA), not the main one (PTA), in order to get the correlated effect you are seeking.
minor ...
2
votes
Best practice between using LEFT JOIN or NOT EXISTS
In Oracle
NOT IN and NOT EXISTS clauses are not the same functionally or performance wise and,therefore, should be used appropriately.
in some cases they are totally different the NOT EXISTS will ...
- 131
2
votes
Accepted
Full text search on two tsvector columns
In parts similar to my last answer:
SELECT a.id, a.article_title AS title, a.pub_date, a.web_id
c.city_points, c.city_names
FROM article a
LEFT JOIN (
SELECT ac.article_id AS id
...
- 170k
2
votes
Select number of times 2 specific values show up for one record id
Assuming that there is a unique constraint on (Order_Number, SKU):
SELECT
sk1.Order_Number
FROM
dbo.Orders AS sk1
JOIN
dbo.Orders AS sk2
ON sk1.Order_Number = sk2.Order_Number
...
- 96.8k
2
votes
Select number of times 2 specific values show up for one record id
You can do this in two different ways:
1) Use EXISTS
SELECT
Order_Number
FROM
(-- Get the list of Order_Number
SELECT DISTINCT
Order_Number
FROM
Orders
) AS onr
...
- 13.2k
2
votes
What order does Oracle's EXIST condition compare the sub-query results?
I would advise, don't try to out-think the optimizer like this. It doesn't always execute your query how you think it would be done based on how you wrote it. It may be able to take advantage of ...
- 121
Only top scored, non community-wiki answers of a minimum length are eligible