Questions tagged [exists]
The exists tag has no summary.
62 questions
0
votes
2
answers
55
views
PLSQL query to return the trackingcodes only if all its cashout_trackingcodes belong exclusively to that trackingcode
I have a table with below structure:
create table TEST_REFUND_CASHOUT
(
f_gdate DATE,
trackingcode VARCHAR2(4000),
cashout_trackingcode VARCHAR2(4000),
cashout_date ...
- 1,502
0
votes
1
answer
1k
views
How does different forms of EXISTS() affect performance in MS Sql Server?
I've been told that when checking for the existence of a row in a table, this will work:
EXISTS(SELECT 1 FROM....)
but that a "better" way in regards to speed performance of the query would ...
- 119
0
votes
2
answers
239
views
How to apply JOIN only if there are rows available
Given the following database:
CREATE TABLE "Activity"
(
"id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
"designation" TEXT NOT NULL
);
CREATE ...
- 109
0
votes
1
answer
165
views
Set variable on multiple case within each group
Say I have below table
cis
aid
cid
apid
ntime
buid
flg
jid
30
1208
229067737
1026
9
DDDD400C
0
0
30
1209
229067737
1026
11
DDDD400C
0
0
30
0
229067737
1026
12
DDDD400C
1
100
30
1210
229067737
1026
13
...
- 197
0
votes
2
answers
83
views
I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments
I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both
This ...
5
votes
2
answers
509
views
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,
...
- 1,665
6
votes
1
answer
1k
views
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 ...
- 173
2
votes
2
answers
3k
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
-1
votes
2
answers
143
views
MYSQL get rows not axisting in other table
I have two tables with 1 identical data column for comparison.
One table "species" is a wide list of species and each data row has one unique id called "suid" add. to its primary ...
- 1
4
votes
3
answers
19k
views
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)...
- 41
0
votes
2
answers
4k
views
How to check existence in Oracle without scanning the entire table?
Normally, to check existence in Oracle I will do:
SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'
However, if the foo table contains multiple rows where bar='baz', this query needlessly scans through the ...
- 207
0
votes
1
answer
107
views
Optimise XOR search on int list
I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int).
Another large table (TableB) contains the same ID column with some other ...
- 103
3
votes
1
answer
63
views
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 ...
- 33
4
votes
2
answers
5k
views
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 ...
- 81
-1
votes
1
answer
41
views
How to check existence of a value in between two columns of the same table
I have a table bills having columns id, starting_bl_no, ending_bl_no.
How can I check a new bill number input is already exist in the table?
id starting_bl_no ending_bl_no
1 100 ...
1
vote
1
answer
227
views
Weird error on creating table and selecting rows from table
I have db and I am trying to create table called "sales_invoice_grid"
I am having bellow scenarios:
error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it ...
- 111
1
vote
1
answer
209
views
Exist on table from linked Server giving Error 4104
I am comparing two tables on different Servers and the query I am running looks like this:
Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
Where Exists( Select * from [ServerB].[TheirDB].[...
- 123
1
vote
2
answers
114
views
Why my query does not return correct data (NORTHWIND)?
select *
from orders o
WHERE EXISTS
(
select 1
from orders o2
where o2.EmployeeID = o.EmployeeID
AND YEAR(o2.OrderDate) = 1997
and MONTH(o2.OrderDate) = 9
AND DAY(o2....
- 13
0
votes
1
answer
308
views
Update + where not exist
once again, I come here for your rescue.
how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of ...
- 1
9
votes
3
answers
60k
views
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 ...
- 293
11
votes
1
answer
36k
views
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....
- 113
3
votes
0
answers
188
views
"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 ...
- 555
5
votes
1
answer
577
views
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 ...
- 105
21
votes
3
answers
14k
views
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)
...
- 3,009
0
votes
1
answer
53
views
Returning data based on if another column in another table is NULL
I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
- 3