SQL query to INTERSECT two columns from same table:-CodePudding

I have a table of Employees that looks like this:

column_name   | column_id |
employee_id   |     1     |
first_name    |     2     |
last_name     |     3     |     
department_id |     11    |

The Employees Table also has a foreign key that references the primary key on Departments Table:

column name   | column id |    
department_id |     1     |

My goal is to use an INTERSECT to show all Employee names that are used as both a first and last name. (i.e: If 'Lee' is a first and last name, I want to display his name specifically).

I am showing the Departments Table because I am not sure whether or not a JOIN is required to get the results I want.

First, I got the full list of all first names and last names:

SELECT e.first_name, e.last_name
FROM Employees e

INTERSECT

SELECT e2.first_name, e2.last_name
FROM Employees e2;

But I am wanting only the names that appear in BOTH the first_name and last_name columns.

I have tried the following:

SELECT e.first_name, e.last_name
FROM Employees e
WHERE e.first_name = e.last_name

INTERSECT

SELECT e2.first_name, e2.last_name
FROM Employees e2
WHERE e2.first_name = e2.last_name;

And no rows were selected.

I also tried to use a JOIN:

SELECT e.first_name, e.last_name
FROM Employees e
JOIN Departments d ON d.department_id = e.department_id

INTERSECT

SELECT e2.first_name, e2.last_name
FROM Employees e2
JOIN Departments d2 ON d2.department_id = e2.department_id;

And this selected ALL the rows.

I am also wondering if there is a way to INTERSECT these columns without using a WHERE clause at all?

CodePudding user response：

How about something like this:

-- get intersection of first and last name sets
select distinct first_name as first_and_last_name from employees
intersect
select distinct last_name as first_and_last_name from employees;

or this:

-- get first names that are also last names
select distinct first_name from employees
where first_name in (select distinct last_name from employees);

There is no reason to worry about the department table unless you want to include data from that table in your result set or use it to filter your result set.

CodePudding user response：

It helps to build up your query step-by-step.

We'll start by getting a list of names that are both first and last name. This is where your INTERSECT comes in.

SELECT e.first_name
FROM   employees e
INTERSECT
SELECT e.last_name
FROM   employees e;

Now, you have that list. Next, you want to show employee details for any employee whose first or last name is anything on that list. Think of that as "show employee details WHERE (some criteria)". We'll start with the employee details query and add the criteria later, which will include the INTERSECT query above.

So, a basic query of employee details would be:

SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE ???  -- we need to plug the first query in here somehow.

Then, putting it together...

SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE  (
         e.first_name IN 
           ( SELECT e.first_name
             FROM   employees e
             INTERSECT
             SELECT e.last_name
             FROM   employees e )
OR       e.last_name IN 
           ( SELECT e.first_name
             FROM   employees e
             INTERSECT
             SELECT e.last_name
             FROM   employees e )
        )

Notice we've repeated that INTERSECT part and repetition is bad, so we'll make that a common table expression (think "WITH clause")

WITH   names (name) AS
           ( SELECT e.first_name name
             FROM   employees e
             INTERSECT
             SELECT e.last_name name
             FROM   employees e )
SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE  (
         e.first_name IN ( SELECT name FROM names )
OR       e.last_name IN ( SELECT name FROM names )
        )

Something like that should do it.

CodePudding user response：

With EXIST :

SELECT first_name FROM employees e WHERE EXIST (SELECT * FROM employees ee WHERE ee.last_name = e.first_name)