Skip to main content
22 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 ...
Tom V's user avatar
  • 15.7k
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 ...
Evan Carroll's user avatar
  • 63.9k
10 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 ...
pim's user avatar
  • 440
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'...
Daniel Vérité's user avatar
10 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 ...
Laurenz Albe's user avatar
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 ...
Rick James's user avatar
  • 78.6k
4 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 ...
David Buttrick's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Danielle Paquette-Harvey's user avatar
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 ...
Charlieface's user avatar
  • 13.7k
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
Akina's user avatar
  • 20.1k
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 ...
Hannah Vernon's user avatar
  • 70.2k
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 ...
AMG's user avatar
  • 1,362
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 ...
Scott Hodgin - Retired's user avatar
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 ...
David Browne - Microsoft's user avatar
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 ...
3 votes
Accepted

Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?

The claim is still true, but it was only ever a concern for long hard-coded IN expression lists. The issue is primarily around the extra compilation time, although weird query plans can also result, ...
Charlieface's user avatar
  • 13.7k
3 votes
Accepted

How does different forms of EXISTS() affect performance in MS Sql Server?

To be sure, you would have to check the execution plans. But it's almost certain, given how trivial an optimization it is, that the compiler will always discard the following in the top level of an ...
Charlieface's user avatar
  • 13.7k
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 ...
ypercubeᵀᴹ's user avatar
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 ...
joanolo's user avatar
  • 13.5k
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 ...
eaolson's user avatar
  • 121
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 ...
sam's user avatar
  • 131
2 votes
Accepted

Caveats of OUTER JOIN on nested JSON value

Use NOT EXISTS: SELECT * FROM (VALUES ('id1'), ('id2'), ('id3')) AS v(id) WHERE NOT EXISTS (SELECT FROM content WHERE json_data ->> 'elementId' = v.id); Or if you prefer a join: SELECT v.id ...
Erwin Brandstetter's user avatar
2 votes

Is this DELETE ... WHERE EXISTS query plan O(n*2) or O(n^2)?

That index is unlikely to be useful, unless you have well over 100GB of RAM. Otherwise, hitting a random uncached index leaf page over 2.5 billion time will be a massive problem. This deletion effort ...
jjanes's user avatar
  • 40.4k
1 vote

How to visualize joins between inner queries to their respective outer queries?

Just break it down into stages and consider what the output of each stage represents SELECT * FROM Ninjas AS N INNER JOIN NinjaWeapons AS NW ON N.WeaponId = NW.WeaponId This can be thought of as ...
Martin Smith's user avatar
  • 85.4k
1 vote

Unexpected results from MariaDB IF EXISTS ... THEN

SELECT COUNT(*) always returns at least one record (maybe with zero). So IF EXISTS is always true. You may: Replace SELECT COUNT(*) ... with SELECT 1 ... Replace IF EXISTS(SELECT ... with IF 0 < (...
Akina's user avatar
  • 20.1k
1 vote

Which Plan is Better? WHERE EXISTS Instead of INNER JOIN

if this new plan ... is superior ... It depends on how many rows end up in the temporary table and how many rows end up being compared to it. The JOIN approach might force the query planner to ...
David Spillett's user avatar
1 vote

WHERE IN subquery too slow

If I understand correctly, you are getting count to satisfy your Pagination requirements. Generally, two separate queries are executed; one to get the 10 records, and another to get the total count. ...
Madhur Bhaiya's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible