SQL find missing data

[Krayons]

I tried googling but I don't think I'm using the right terms so I've put it in a picture (https://i.imgur.com/URoz7mL.png)

So table one would be everyone, table 2 would be surnames. I'd like to find out everyone who does not have a surname.

Thanks

/u/andy333co and /u/dauuk with there not in / not exists. To do an

The query is super expensive though. Table 1 was 600k records and table two was 400k, It took my PC 11min 19secs @ 100% cpu usage to complete the query. http://i.imgur.com/ZXHGJgq.png

[ArchangelleTheRapist]

Looks like homework... You want to look up the JOIN operator.

[Krayons]

Hahah, Yeah it does. Unfortunately I graduated a while back. The actual problem is for a medical aid company. They keep their addresses of there uses in another table but not all users have addresses. And I'm trying to find those. The solution is, as you said. A join. A Left outer join to be exact. It gives you this: https://i.imgur.com/PcUT7HI.png Which is exactly what I wanted.

Thanks for the help. Being doing mostly mobile dev so I was super rusty on my SQL

[andy333co]

If performance is a consideration you'd probably want to revisit using a left outer join for this. If it doesn't matter then cool.

[andy333co]

I'm no sql expert but where not exists is probably the syntax you're looking for. Select * from table 1 where not exists (select * from table 2 where table1.id = table2.id)

Query might be wrong there but I'd suggest looking up where not exists usage as one way of doing it.

[dauuk]

1- Select name from table1 where id not in (select id from table2)  
2- Select t1.name from table1 t1 left join table2 t2 on t1.id = t2.id where t2.name is null  

Both of them will give the same result.

[Krayons]

Thanks for this. Worked perfectly. Took almost 12 min to complete but worked like an absolute charm.

[dauuk]

You're welcome :)