SQL Set Operators - Selecting rows from tables with different columns

Question

I'm using Oracle 10g, and I'm trying to select rows from one table that do not appear in the other table in the query using a set operator.

I'm trying to select id, last_name and first_name columns from an employee table in which these rows do not appear in a job_history table.

The only common column in these 2 tables is the id column. But I want to display the names as well.

I have tried:

SELECT 
    id, last_name, first_name 
FROM 
    employees

MINUS

SELECT 
    id, TO_CHAR(null), TO_CHAR(null) 
FROM 
    job_history;

Which doesn't produce desired result.

However, if I didn't want to display the names from the employee table, I use:

SELECT id FROM employees
MINUS
SELECT id FROM job_history;

Which gives me half of the result, except for that I want the names from the employee table.

Any advice?

Rahul · Answer 1 · 2016-08-01T20:21:02.890

3

Why can't you just use NOT IN like

SELECT id, last_name, first_name FROM employees
WHERE ID NOT IN (SELECT id FROM job_history);

You can as well try LEFT JOIN like

SELECT e.id, e.last_name, e.first_name 
FROM employees e LEFT JOIN job_history jh
ON e.ID = jh.ID
WHERE jh.some_other_column IS NULL;

edited Aug 01 '16 at 20:21

answered Aug 01 '16 at 20:18

Rahul

1

This seems like the most sensible answer. – Gordon Linoff Aug 01 '16 at 20:20
I know there are easier ways of producing the result, but I'm trying understand how to use set operators. – ethane Aug 01 '16 at 20:26
The NOT IN solution will produce the wrong result if there are NULLs in the job_history.id column. This column should be NOT NULL from what we can tell, but that should be checked first. – Aug 01 '16 at 20:57
@mathguy, since it's a ID column and will have `NOT NULL` constraint. – Rahul Aug 01 '16 at 20:59
I agree and I was suggesting the same, but it never hurts to check first. Do you know for sure this column has a `NOT NULL` constraint? It should doesn't mean it does! :-) – Aug 01 '16 at 21:05
@Ethan - a good understanding of "how to use set operators" should include an understanding of when they SHOULDN'T be used. The most natural solution here is what Rahul posted (the NOT IN solution), if the id column is NOT NULL. The second best in my opinion is the join solution. Set operations are not needed in the two best solutions for your problem... – Aug 01 '16 at 21:09
@mathguy, feels you are surrounded with confusion ... OP wants non-common rows from both the table and not the common ones. Ahh!! You already got it. – Rahul Aug 01 '16 at 21:14
I know, I posted a stupid answer and three stupid comments, just deleted them... leaving only those that still make sense. Sorry! – Aug 01 '16 at 21:15

score 2 · Accepted Answer · answered Aug 01 '16 at 20:19

2

You can use a inner join on the select result

select a.id,   a.last_name, a.first_name
from employees a 
inner join ( 
SELECT id FROM employees
MINUS
SELECT id FROM job_history ) x on x.id = a.id

answered Aug 01 '16 at 20:19

ScaisEdge

This produced the correct result for me. Could you please provide an explanation for `x on x.id = a.id`? I've never come across this syntax. – ethane Aug 01 '16 at 20:29
Yes.. of course .. the x is the alias for the table created using the subselect .. and on x.id = a.id is the join clause between the table employees (alias a) and the subselect table .. hope is clear .. otherwise .. ask me again – ScaisEdge Aug 01 '16 at 20:32
Not sure why the `minus` is needed here. (Of course, without it the solution doesn't use set operations, but the `minus` used in this solution is unnecessary.) – Aug 01 '16 at 21:06
@mathguy the OP use minus for obtain the list of the id in employees not in job_history .. .. (could be there are others way but this is what came from the question) – ScaisEdge Aug 01 '16 at 21:10
Sorry, I posted a stupid "answer" and three stupid "comments" already, just deleting them. :-) all my mistakes – Aug 01 '16 at 21:13
@mathguy .. I fully understand and appreciate that you have sent this clarification comment .. this is much appreciated on my part because because not always in SO users are well educated . like you in this case . however, what happened to you happens a little at all .. – ScaisEdge Aug 01 '16 at 21:17

2 Answers2