Hot answers tagged

58

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 columns from A. In SQL Server, the second variant is slightly faster in a very simple contrived example: Create two sample tables: CREATE TABLE dbo.A ( A_ID ...


32

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 explained in my answer to this related question: How (and why) does TOP impact an execution plan? Using EXISTS introduces a row goal, where the optimizer produces an ...


25

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 the result of a COUNT(*) and then test that variable to see if it's more than 0. So... With a small row goal it will avoid blocking operations, such as building ...


23

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 it's only used as a filter (as opposed to having them available with NULL values). Many years ago (SQL Server 6.0 ish), LEFT JOIN was quicker, but that hasn't ...


18

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 example, consider that a logical semi-join (such as a sub-query introduced with EXISTS) shares the overall theme: it should be optimised to find the first ...


11

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 list> is equivalent to a <value expression> that is an arbitrary <literal>. This confirms that by * not being equivalent to an arbitrary literal ...


6

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 EXISTS operator gets executed in a nested loop fashion. Whereby it is executed on a per row basis (which I suppose makes sense). Example execution plan ...


5

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 rows) The form of the input query (i.e. your question) For (4): The "not exists" plan encourages a seek based plan on table B. This is a good choice when ...


4

The (somewhat trivial) answer seems to be: Cars ⋉ Ford Relational algebra, semijoin (Wikipedia) Alternatively (in response to a comment): πR(R ⋈ S)


4

select l.* from location as l --- find all locations where not exists --- where there isn't ( select * from location_flag as lf --- a flag where lf.location_id = l.id and lf.flag_id = 1 --- with 1 and lf.value = 'YES' ...


4

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's impossible to create a database if you're not already connect to another database. This is documented as: When this option is used, the database named ...


4

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 LIKE 'mae%' AND p.start_time <= i.datetime AND i.datetime < p.end_time ) ; I changed the BETWEEN to >= start_time and < ...


3

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 order table. That totals to 228. Your query is returning all orders where the employeeid matches those two id's. Alter your query to include a check for o2....


3

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 from a where not exists ( select * from B_Temp where a.id = b.id ) You would want to disable the index on the id column before you load the data, and re-enable it ...


3

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 CREATE TABLE but using CREATE TEMPORARY TABLE): CREATE TEMPORARY TABLE A ( id BIGINT NOT NULL AUTO_INCREMENT, number BIGINT NOT NULL, PRIMARY KEY (...


3

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 * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey ) UNION SELECT NoteKey, Title FROM Note However, if you want to specify the ...


3

Example 4 has the fewest scans and reads: Example 1 SQL Server parse and compile time:     CPU time = 4 ms, elapsed time = 4 ms. SQL Server Execution Times:     CPU time = 0 ms, elapsed time = 0 ms. example1 Id FirstName ----------------------------- 1 2 Aaron 1 ...


3

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 argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one ...


3

Access doesn't support the IF EXISTS syntax in queries; I think your error is coming from that. It's also very difficult to do any sort of procedural logic using Access SQL. Your alternatives are to run the ALTER TABLE statement and handle the error elsewhere or use VBA to conditionally run the statement.


3

If you are browsing in Excel and want to be able to see dimension attribute members that do not have corresponding rows in a fact table (measure returns nothing), there is a pivot table setting you can use. Access the PivotTable Options dialog box by right clicking on the pivot table and choosing PivotTable Options. On the Display tab, check the box next ...


3

They are doing two different things. The first one displays products where the ProductQty is equal to 89. It is essentially the same as: select * from product where ProductQty = 89 and productname is not null; The second one displays all products (regardless of their quantity) if there is at least one product with ProductQty = 89 because the sub-...


2

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?) But this is a SQL to get around the syntax error you have: WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100 ) SELECT sum(n) FROM t; I would like to ...


2

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 AND sk1.SKU = 'abc' AND sk2.SKU = 'ghi' ; Tested at dbfiddle.uk. Note how with an index on (SKU, Order_Number) the execution plan starts with 2 index seeks and ...


2

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 WHERE -- Check that there is an 'abc' associated EXISTS ( SELECT * FROM Orders os1 WHERE os1.Order_Number = onr.Order_Number and os1.SKU ...


2

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 indexes or alternate join paths. And as mathguy said above, the difference between EXISTS and IN is much better than it used to be. I would actually expect if you ...


1

Not a fair test. Your example is about as close as you can get between using JOIN to using EXISTS. Also, it probably gave a different result -- What if there are 2 Johns? The JOIN would count both; the EXISTS would count only once. EXISTS is a "semi-join". It is better than JOIN in cases where it can stop short of scanning for all rows that match. ...


1

Your query can be collapsed to the single JOIN without subselects: UPDATE student_activity AS c LEFT JOIN student_activity AS b ON c.student_id = b.student_id AND c.class_type = b.class_type AND c.date > b.date WHERE b.date IS NULL SET c.first_attendance = c.date ; I hope ...


1

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 another table, then this might be optimal: Table `nums` contains the `n` values. ALTER TABLE tbl ADD INDEX(id); -- if it does not already exist. SELECT ! ...


1

You need a join or a correlated subquery (with `EXISTS), in order to compare rows: SELECT SUM(c.available_supply * h.price) AS market_cap_usd_all FROM coins AS c JOIN hist_all AS h ON c.id = h.coins_id WHERE h.price <> 0 AND ( h.second_coin = 'USD' OR h.second_coin = 'USDT' AND NOT EXISTS ( SELECT 1 ...


1

There are some redundant JOINs between the 2 tables, and using too many OR conditions with nested queries sometimes overwhelms the SQL Query Optimiser. After cleaning up and organising the code, I've come up with the following (hopefully equivalent) alternative: DECLARE @a INT ,@b BIT ,@c INT ,@d INT ,@e INT; SELECT SUM(CNT) AS [C] FROM ( ...


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