NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.
Questions tagged [not-exists]
403 questions
3
votes
3 answers
Create table if not exists syntax db2
I wish to write a SQL script that will check whether the table/sequence exists or not before create the table/sequence.
I tried Google and get some solution that work for other people but not work for me:
Method 1:
SELECT *
FROM tableA
WHERE …
Panadol Chong
- 1,793
- 13
- 54
- 119
3
votes
2 answers
Why does SQL NOT EXISTS return 1 record of all NULL values
SQL Fiddle on the following:
create table tbl( col1 int, col2 int, col3 int);
insert into tbl values(1,1,1);
insert into tbl values(1,1,1);
select sum(col1) c1, sum(col2) c2, sum(col3)c3
from tbl
where not exists (
select 2 as c1, 2 as c2, 2 as…
Kairan
- 5,342
- 27
- 65
- 104
3
votes
1 answer
There is a column named "ap" in table "session", but it cannot be referenced from this part of the query
I've got a table "session":
--------------------+-----------------------------+------------------------------------------------------+-----------+---------------------+----------
id | integer | NOT NULL DEFAULT…
Tony
- 3,605
- 14
- 52
- 84
3
votes
1 answer
Insert into table if record does not already exist
For some reason, this is giving me the "cannot insert duplicate record into table" error.
INSERT INTO [DMS].[dbo].[Deductions]
(
CustomerID,
DeductionCode,
DeductionDescription
)
SELECT …
JJ.
- 9,580
- 37
- 116
- 189
3
votes
1 answer
How to use not exists in a sql query with w3schools?
I have an issue with not exists sql query at w3schools
I want to select all customers that work with shipperid = 1 BUT not shipperid = 3.
I tried the following:
select o1.customerid, o1.shipperid
from orders o1
where o1.shipperid=1 and not…
CHEBURASHKA
- 1,623
- 11
- 53
- 85
3
votes
2 answers
Performance with NOT EXISTS - t-sql query
This (modified for simplicity) query is part of a larger query, and joined on date with other selects. However I have pinned this section to be dog slow. Say I have a UserLoginHistory-table that logs each login for a user. For each user I want the…
cederlof
- 7,206
- 4
- 45
- 62
3
votes
3 answers
NOT EXISTS clause in SQL
I have been stuck on a query and I am really not able to think how does the execution takes place, any help will be highly appreciated :
The query is devised to find the details of the employee who works on all the projects.
The query is :
SELECT…
user2106410
- 69
- 1
- 2
- 6
3
votes
6 answers
MySQL - NOT IN produce unwanted result
I have tables below:
user
+-----------------------------------------------+
| user_id | username | Password | ... |
+-----------------------------------------------+
| 1 | a | *** | ... …
Tepken Vannkorn
- 9,648
- 14
- 61
- 86
3
votes
2 answers
Criteria api query in Hibernate with not exist
Im trying to write query, which returns me list of Drivers wich is not assigned to route.
My database a set up as following.
Route:
route_id
user_id//specified as driver
User:
user_id
role // need to select user, which is Driver role
Only route…
kuldarim
- 1,096
- 8
- 21
- 44
3
votes
2 answers
in bash find all files in flat directory that don't exist in another directory tree
I have many files in a directory A.
Some of those files exist in a directory tree with sub-directories B/B1, B/B2, B/B3, B/B4, ...
Note that some files have spaces in their names.
For example:
in directory A:
there's a file named A/red…
martin jakubik
- 4,168
- 5
- 29
- 42
2
votes
2 answers
How to write Linq (or lambda) statement to produce all element that are NOT IN (or NOT EXIST)
Here is my scenario (Tables):
Orders
======================
Id (int)
description (varchar)
Products
======================
Id (int)
description (varchar)
OrderProductXREF (cross reference table)
======================
ProductId (int)
OrderId…
bobetko
- 5,019
- 14
- 58
- 85
2
votes
1 answer
Using WHERE NOT EXISTS with dummy table not working on Apache Derby Database
So I'm using Java with Apache Derby, and I'm trying to insert a record, but only if a record with the same key does not already exist, because all the values I want to exist in my code rather than the database I use derbys dummy table (analogous to …
Paul Taylor
- 13,411
- 42
- 184
- 351
2
votes
2 answers
Will the following two queries give the same output consistently?
We want to find the deptno of those departments having employees who can do some work done by employee in depertment 20.
SELECT deptno
FROM dept
WHERE EXISTS(SELECT *
FROM emp x
WHERE x.deptno = 20
…
Pop Stack
- 926
- 4
- 19
- 27
2
votes
3 answers
Display customers who haven't made a purchase in less than 30 days
I'm trying to list all the customers who haven't had a purchase in the past 30 days with the following query:
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM purchases p
WHERE c.customer_id = p.customer_id
AND…
Pugzly
- 844
- 3
- 14
2
votes
2 answers
Mysql IF NOT EXISTS then syntax
I can't manage to produce a working mysql query right now for what I want. I got as close as
IF NOT EXISTS(SELECT *
FROM Users
WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4')
UPDATE Users
SET…
Andrew
- 203
- 2
- 10