Stack Overflow
Stack Overflow

Questions tagged [sql-except]

The SQL EXCEPT operator combines two queries and returns those rows that are in the first query but not in the second. It represents the difference between two sets.

The EXCEPT operator has the same restrictions as the UNION operator with regards to the two queries that are combined. Both queries need to have the same number of columns and the data types of the columns need to match.

In Oracle the EXCEPT operator is called MINUS

41 questions
66
votes
15 answers

SELECT * EXCEPT

Is there any RDBMS that implements something like SELECT * EXCEPT? What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else. Almost daily I complain to my coworkers that someone…
Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94
26
votes
2 answers

Is there a fundamental difference between INTERSECT and INNER JOIN?

I understand, that INNER JOIN is made for referenced keys and INTERSECT is not. But afaik in some cases, both of them can do the same thing. So, is there a difference (in performance or anything) between the following two expressions? And if there…
Jere
  • 1,196
  • 1
  • 9
  • 31
18
votes
2 answers

EXCEPT keyword in Oracle

I'm trying to use the EXCEPT keyword in Oracle 10.1.0.2.0, but kept getting error 'Unknown Command'. I've tried googling around and someone said the keyword is MINUS, so I used MINUS, instead, but I still got the same error. Any idea? Thanks. So…
0x56794E
  • 20,883
  • 13
  • 42
  • 58
17
votes
3 answers

Syntax error when using except in a query

This query works: mysql> SELECT s.sno FROM students s; +------+ | sno | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00…
Cratylus
  • 52,998
  • 69
  • 209
  • 339
4
votes
1 answer

SQL-Union ALL and Except

I am seeing a strange behavior when I do except and union statements in SQL. I have two tables Select * from #old Data looks like this oid1 oid2 co 1 11 1 2 22 1 3 33 1 4 55 1 Select…
Sarah
  • 1,199
  • 2
  • 21
  • 42
3
votes
1 answer

Getting Table Name in Cursor Results , Android, SQLite

I am writing an Android app involving cars and parts. The thing I am seeking to do is offer a ListView of all the parts in the db excluding the parts already assigned for a specific car. I am able to get the results I want with the following query…
user9329083
3
votes
1 answer

SQL, combine two tables with distinct values only (based on only three fields, not entire row)

I'm trying to combine three tables, Application, Audio, Video. They all have the same schema, but there are 'duplicates' in Audio and Video. Where an entry in Video will always also show in Audio. I put duplicates in quotes as the entire row isn't…
M Do
  • 33
  • 1
  • 4
3
votes
2 answers

SQL: Using Not Exists with Nested Query

I'm having trouble understanding how this query works. It is supposed to return the name of customers who have ordered ALL items. R refers to the table of item orders made by customers which contains the customer id (cid) and item id (iid). I refers…
roverred
  • 1,841
  • 5
  • 29
  • 46
2
votes
3 answers

How to avoid selection of rows of parents that don't have a child, if they have children in other rows?

I want to avoid selecting the cases where there is a blank child, but only where a parent-child relationship already exists. So in this example, A-D would stay but the empty rows with A should get removed. As C doesn't have any children in any row,…
Ricardo Francois
  • 752
  • 7
  • 24
2
votes
2 answers

Why there is a sort operator in execution plan when EXCEPTed queries are clustered tables?

I'm building a Data Warehouse and I found a problem in two tables data comparison statement. I use EXCEPT operator to compare the tables which have clustered indexes (normal int field as key). My problem is that in query execution plan there are…
dominjas
  • 31
  • 4
2
votes
2 answers

Find missing entries in a SQL table conditional on criteria

I have modest simple SQL experience (using MS SQL server 2012 here) but this evades me. I wish to output distinct names from a table (previously successfully created from a join) which have some required entries missing, but conditional on the…
Graham
  • 23
  • 3
2
votes
2 answers

Is there any way to run an except query on MSSQL that uses only part of the columns?

What I need to do is the following: I have in my database a table like this: idx | name | age ------ ---------- ------- 1 | John | 18 2 | Marry | 19 3 | Eric | 17 Then I get a secondTable: name | age ------…
Ma'or
  • 95
  • 1
  • 2
  • 11
2
votes
3 answers

EXCEPT command - Find sailors who’ve reserved all boats

I am reading a textbook and I do not understand this query: Find sailors who’ve reserved all boats. We have 3 tables: Sailors: sid, sname, rating, age (primary: sid) Boats: bid (primary: bid) Reserves: sid, bid, day (primary, sid, bid, day)…
NNguyen
  • 113
  • 1
  • 6
2
votes
4 answers

Filter a group in mysql

I have following sample data from a table Id Dsc 500001 INSURED 500001 THIRD PARTY 500001 THIRD PARTY 500001 THIRD PARTY 500002 INSURED 500002 THIRD PARTY 500003 INSURED 500004 BROKER 500005 CLAIMANT I wish to extract those Ids for…
KMK
  • 25
  • 4
1
vote
1 answer

PostgreSQL: How to check if a list is contained in another list?

I'm working with PostgreSQL 13. I have two tables like this: permission_table name permission Ann Read Invoice Ann Write Invoice Ann Execute Payments Bob Read Staff data Bob Modify Staff data Bob Execute Payroll Carl Read…
Carpo Crates
  • 78
  • 8
1
2 3