Questions tagged [exists]
60 questions
129
votes
5 answers
Best practice between using LEFT JOIN or NOT EXISTS
Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?
What is benefit to using one over the other?
If none, which should be preferred?
SELECT *
FROM tableA A
LEFT JOIN tableB B
ON A.idx = B.idx
WHERE B.idx IS NULL
SELECT…
Michael Richardson
- 1,465
- 2
- 10
- 9
40
votes
2 answers
IF EXISTS taking longer than embedded select statement
When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, the select query returns no records.
Any idea…
Chris Woods
- 1,791
- 1
- 16
- 22
21
votes
3 answers
Wrapping query in IF EXISTS makes it very slow
I have the below query :
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
The above query completes in three seconds.
If the…
TheGameiswar
- 2,999
- 4
- 30
- 50
11
votes
1 answer
pg_restore ERROR: "Relation does not exist" and creating new database
I have made a backup of my specific tables that I want to restore into a new database using:
call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2
And I have no problems.
I then want to restore…
Ian23
- 113
- 1
- 1
- 4
11
votes
1 answer
Does the SQL Spec require a GROUP BY in EXISTS ()
Microsoft currently permits this syntax.
SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
SELECT *
FROM ( VALUES (1),(1) )
AS t(x)
WHERE g.x = t.x
HAVING count(*) > 1
);
Notice that there is no GROUP BY in the EXISTS clause, is that…
Evan Carroll
- 65,432
- 50
- 254
- 507
9
votes
3 answers
How to check if all elements of an array exists in a table?
Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,... 392] where n is bigger than 100k entries. Some of this entries are repeated.
I need to check if all of these registers are contained in a MySQL table…
Daniel Santos
- 293
- 2
- 4
- 9
6
votes
1 answer
EXISTS() vs EXISTS() = TRUE in Postgres
Faced weird behaviour with EXISTS (also applies for NOT EXISTS) generating different execution plans for
WHERE EXISTS(...)
EXPLAIN ANALYZE
SELECT * FROM books
WHERE EXISTS (SELECT 1 FROM authors WHERE id = books.author_id AND name LIKE 'asd%');
|…
scorpp
- 173
- 5
5
votes
2 answers
Is this DELETE ... WHERE EXISTS query plan O(n*2) or O(n^2)?
I'm trying to perform a common task, deleting duplicates from a table with the aim of adding a unique constraint.
CREATE TABLE IF NOT EXISTS item_identifier (
pk BIGSERIAL PRIMARY KEY,
prefix INTEGER NOT NULL,
suffix …
Joe
- 1,655
- 2
- 11
- 14
5
votes
1 answer
SQL Server OR operator causing massive loop join
The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...).
I used live execution to verify that there is a nested loop join between the tables for the OR statement and then…
miledba
- 105
- 1
- 5
5
votes
2 answers
Howto combine UNION with EXISTS?
I have a query that looks like this:
SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
Which works fine. My trouble start when I try to add an EXIST to it:
SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey,…
Bjørn Fridal
- 65
- 1
- 1
- 4
4
votes
3 answers
How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?
The below is a valid TSQL statement. But I want to understand how SQL Server processes it.
DELETE A
FROM table1 WITH (NOLOCK)
WHERE
EXISTS
(
SELECT 1
FROM
table2 B WITH (NOLOCK)
WHERE
B.id = A.id
)
Because…
a_shie
- 41
- 1
- 1
- 4
4
votes
2 answers
Which Plan is Better? WHERE EXISTS Instead of INNER JOIN
Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.
Logical reads plummeted, but plan cost and memory performance seem worse. Duration and CPU are slightly…
BBaggins
- 81
- 1
- 3
3
votes
1 answer
Help filtering out records in MYSQL
I hope that someone can help me.
I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care.
Each Koala has their own name with a fate that has happened with it.
I need to extract…
Rod-Miller
- 33
- 3
3
votes
0 answers
"FOR UPDATE" in an "EXISTS SELECT" statement
Is there any potential problem in using (in a transaction)
SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )
i.e. having SELECT ... FOR UPDATE within an EXISTS?
MySQL does certainly some optimizations for EXISTS since it doesn't have to…
Déjà vu
- 555
- 2
- 8
- 19
3
votes
1 answer
Full text search on two tsvector columns
I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are:
article.article_title
keyword.keyword
Where there is a many-to-many relationship between the two. I think I've got most…
sammy88888888
- 145
- 1
- 5