For questions involving the MINUS operator, which is the Oracle’s dialect counterpart of the SQL standard’s EXCEPT. Both MINUS and EXCEPT are based on the set operator called DIFFERENCE in relational algebra.
Questions tagged [minus]
10 questions
6
votes
4 answers
Optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no keys on the table).
For that, I would do:
SELECT…
0x4B1D
- 163
- 3
5
votes
3 answers
Database replication and consistency check
I got a question about two databases (in Oracle 10g) that I have, let's call them A and B. A have some information (in various tables) and I want B to get a partial copy of some tables from A, and constantly check changes in A and 'sync' them in…
Marco Aviles
- 115
- 7
3
votes
2 answers
UNION ALL w/ MINUS
Why does:
select 1 FROM DUAL
UNION ALL
select 1 FROM DUAL
MINUS
SELECT 2 FROM DUAL
return only a single 1 rather than 2 rows of 1 in Oracle?
Kevin Meredith
- 559
- 1
- 7
- 14
1
vote
2 answers
Oracle: finding rows without children in a table with a foreign key?
Suppose I have two tables with a foreign key relationship. How can I find the rows in the "parent" table for which there is no corresponding "child" row?
For example:
create table a(x number primary key);
create table b(x number);
alter table b add…
Mark Harrison
- 829
- 5
- 20
- 33
1
vote
1 answer
Oracle Alternative to EXISTS EXCEPT
I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here:
Comparing Columns that can contain NULLS - is there a more elegant way?
I can't get this solution (the one which was accepted) to work in Oracle…
GWR
- 2,847
- 9
- 35
- 42
1
vote
2 answers
How to select students that failed at least at all courses that student with id = 1 failed?
I have the following tables:
STUDENT (student_id, first_name, last_name, birth_date, year , domain)
PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary)
COURSE (course_id, course_name, professor_id)
GRADE…
Eduard Valentin
- 13
- 1
- 4
0
votes
1 answer
ORA-00907: missing right parenthesis on query with subqueries and EXCEPT operator
I am getting
ORA-00907: missing right parenthesis
for the following query:
SELECT PEOPLE.NAME FROM
CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L
WHERE NOT EXISTS
(
(SELECT DISTINCT ARTIST.ID_U
FROM CLIENTS INNER JOIN ADORES ON CLIENTS.ID_L…
PerakR
- 3
- 1
- 2
0
votes
2 answers
CASE Statement - 1st case is being ignored
I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users that are no longer on the database.
The table…
Chris.V
- 231
- 2
- 5
- 12
0
votes
0 answers
Remove tables intersection
Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7
CREATE TABLE IF NOT EXISTS t1 (
id_ INTEGER NOT NULL,
value1 INT NOT NULL
INSERT…
Bruno Lobo
- 127
- 4
0
votes
2 answers
Select items that have never been ordered using the MINUS operator
I have the following two tables:
ITEM (Item#, Item_Name, Unit_Price)
ORDER_ITEM (Order#, Item#, Qty)
Now, how do I list the items that have never been ordered by using the MINUS operator?
stranger
- 193
- 3
- 4
- 13