Ethan Ethan - 4 months ago 9
SQL Question

SQL Set Operators - Selecting rows from tables with different columns

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?

Answer

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