How can I make an intersection of two columns of two tables and get the entire rows?

Question

I have a SQLite table. Let's call it people.

Name	Age
Jane	50
John	80
Alice	46
Mark	25
Harry	33

I have another table work.

Name	work_id
Jane	1
Amanda	2
Filip	3
Alice	4
Jack	5
Harry	6

I'd like to get all rows whose name is in both people and work. But I do not only want the names. In fact I don't care about the names at all. I just need them to find the matching entries. I want the work.work_id and people.age columns of the matching rows. The result should look like this:

work_id	age
1	50
4	46
6	33

Both tables can have hundreds to thousands of entries.

I also need a difference of the two i.e. The rows of work whose name isn't in people. But this should be solvable with the second solution I have outlined below.

I am doing this in Python3 using the builtin sqlite3 module. But this should be a purely SQL problem independent of the SQL client.

What I've tried

The obvious choice is to do an INTERSECT:

SELECT Name FROM people INTERSECT SELECT Name FROM work_id

As I said, I need the Name columns to find the intersection of the tables but I need the rows themselves to get the things I actually want, people.age and work.work_id, not the Names.

---

The internet lead me to subqueries.

SELECT Name, Age FROM people where Name IN (SELECT Name FROM work)

This is a pretty powerful technique but I also need the work_id column of work so this isn't a solution.

Is this comparing each row in people with all rows of work? Is the number of comparisons SELECT Count(*) FROM people × SELECT Count(*) FROM work or is it somehow optimized?

Answer 1

You want to select columns from both tables and this means you need an INNER JOIN:

SELECT w.work_id,
       p.Age
FROM work AS w INNER JOIN people AS p
ON p.Name = w.Name;

For the rows of work whose name isn't in people use NOT IN:

SELECT *
FROM work
WHERE Name NOT IN (SELECT Name FROM people);

See the demo.

Answer 2

SELECT work_id, Age
FROM people AS p
JOIN work AS w ON p.Name = w.Name;


SELECT name, work_id
FROM work
EXCEPT 
SELECT name
FROM people