SQL query help

General Programming
1

hi all,

i’m constructing an SQL query (SQLITE) to check for dates in two tables that don’t exist in a third table. In the code below, tables 2 and 3 contain all valid dates and the intent is to get a list of dates in table 1 that don’t exist in 2 or 3 so they can be removed.

When just comparing just 2 tables, the EXCEPT argument works well.

SELECT date1 FROM table1 
EXCEPT 
SELECT date2 FROM table2

works a treat.

I would have thought that surrounding the 2nd part of the EXCEPT argument in parentheses for more complex arguments would do it but

SELECT date1 FROM table1
EXCEPT (
  SELECT date2 FROM table2
  UNION
  SELECT date3 FROM table3
)

generates a syntax error… (which disappears when i remove the parentheses but then i suspect the result would be from (line 1 except line 2) union line 3, which would be wrong?

Any suggestions?
(* edited for formatting)

2

Actually found the correct answer - for anyone else that needs this, the correct syntax is:

SELECT date1 FROM table1  
EXCEPT 
SELECT * FROM (
  SELECT date2 FROM table2
  UNION 
  SELECT date3 FROM table3
)

-- returns all dates in table1 that don't exist in either table2 or table3

(edited to include result of query)

3

Yup… that was the exact solution I had formulated in my head before I saw you’d solved it yourself…

Except I use EXISTS

4

Apparently exists is an “old guy thing” as thats what I’d have written as well :slight_smile:

5

Yeah… I’d never run across “Except” before to be honest… and to me “Exists” is more readable… Except… Except what? Exists makes “sense” to me

6

I guess EXCEPT == !EXISTS?

In any case works beautifully :wink:

7

Seems like EXCEPT is similar to NOT EXISTS or NOT IN

8

Well EXCEPT allows me to pick up values from one query that don’t exist in another query.

I’ll be the first to admit my SQL is rudimetary, but this does the trick exactly.

looked up EXISTS and not sure that would have done the trick…

9

with correlated subqueries it probably would :slight_smile:

10

I didn’t even knew EXCEPT existed, I would have used EXISTS too :slight_smile:

11

There’s about a million ways to skin a cat… but always good to know more :slight_smile:

So, looking up EXISTS, the definition is “The EXISTS operator is a logical operator that checks whether a subquery returns any row.” - a boolean result.

Keeping in mind i want to return a list of values in one query that don’t exist in another query as my example above, how would I use EXISTS for this?
(it’s academic as the EXCEPT operator does exactly that - semantically similar to NOT IN - but can’t get my head around how you would use EXISTS in my example above and am now intrigued :slight_smile: )

12

probably along the lines of

select * from outertable where not exists ( select column from innertable where innertable.someKeyField = outertable.keyField )

1 Like
13

Sure, I get that… but how would u iterate (in sql)?

Or can u use EXISTS for the entire cursor returned from a sub query rather than a distinct value?

14

yes you can
what Norm posted returns everything that is in the outer table and does not exist in the inner table

15

Ok I think I get it… I misread Norm’s example.

So in terms of my specific problem, using the simpler version with just two tables, to find all dates in table 1 except for those in table 2, using EXISTS would be:

SELECT date1 FROM table1 
WHERE NOT EXISTS (
   SELECT date2 FROM table2 
   WHERE table1.date1 = table2.date2
)

Is that correct?

16

that looks right

17

That’s how I would have wrote it. The question now is: Does it work? :slight_smile:

18

Haven’t had time to check but will do :laughing:

20

Yep it works just fine – but i still prefer the EXCEPT semantics (ie all values in query1 except from those in query2). I’ll put that down to not being an (SQL) old guy :laughing: :laughing:

21

In a paying game, one may want to exclude youngsters (- 21 Y/O for example) … so Except born date after 1999-07 ?