1

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
user13840624
  • 133
  • 8

2 Answers2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
-1
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
Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
branchy99
  • 1
  • 2
  • 1
    Please explain your code. Also note your second query is invalid because EXCEPT requires an identic number of columns in both queries. – Jonas Metzler Feb 08 '23 at 12:17
  • Ok) my level English is elementary, but I will try to write correctly. Thanks for the help and advice – branchy99 Feb 08 '23 at 14:53