CODEWITHSUNDEEP
sqlsubquerywhere-clause
Jerome Dela Cruz
Jerome Dela Cruz

Reputation: 13

Searching a table comparing values from another table (2 different columns)

I have a large table that I'm wanting to select 8 columns out of. I would be comparing it to another table with 2 columns making sure they (said columns) match exactly.

Pseudo Code:

SELECT a, b, c, d, e, f, g, h, i 
FROM table1 t1 
WHERE a AND b are matching the same rows in table 2

I've done this with a similar example, but only had 1 column in table 2 instead of 2 like so:

SELECT a, b, c, d, e, f, g, h, i 
FROM table t1 
WHERE a IN (SELECT * FROM table2 t2)

which gives me the results. But again, I now need to make sure that a AND b match the corresponding rows from table2. I've been searching but cannot find a solution. I've tried INNER JOINS, but I'm getting more results than there should be. Any help would be greatly appreciated as SQL is definitely not my wheelhouse. Also if there are any other clarifications, please let me know.

I think this is simple enough of a question, I hope. Thanks in advance!

In response to the comment:

Table 1 (has 20 million rows)

Address Zip Year Area Name1 Name2 example example
123 Main St 77777 1999 2000 John Doe John Smith data data
456 Main St 88888 2012 2500 James Doe John Smith data data
789 Main St 99999 2018 2800 Michael Doe Michelle Doe data data

Table 2 (has 7500 rows):

Address Zip
123 Main St 77777
321 Smith St 66666
789 Main St 99999
455 Highway 1 44444

Results would be expected (7500 rows)

Address Zip Year Area Name1 Name2 example example
123 Main St 77777 1999 2000 John Doe John Smith data data
789 Main St 99999 2018 2800 Michael Doe Michelle Doe data data

Upvotes: 0

Views: 35

Answers (3)

Jerome Dela Cruz
Jerome Dela Cruz

Reputation: 13

Thanks everyone for the insights and help with this issue of mine. I ended up with a solution that works for me. Also shoutout to Joel Coehoorn who pointed out to look at the results of my query. One thing that happened was the results gave me null values when matching on the zip code as well. Adding in a WHERE clause helped clear it up. Also turns out some of the initial data (7500 addresses and zip codes) were not formatted correctly so that was causing some issues as well. Thanks again Stack Overflow community!

    SELECT PropertyAddressFull, PropertyAddressZIP, c, d, e, f FROM table1 t1
  INNER JOIN table2 t2 ON (t1.PropertyAddressFull=t2.address AND t1.PropertyAddressZIP=t2.propertyzip) 
  WHERE PropertyAddressFull != '' AND PropertyAddressFull IS NOT NULL AND PropertyAddressZIP != '' AND PropertyAddressZIP  IS NOT NULL

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

One option is to JOIN the two tables:

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2 on t1.a = t2.a and t1.b = t2.b

Just be aware, if Table2 can have more than one row with the same a,b value you can end up with duplicate results. Usually the EXISTS() option will perform the best, but sometimes the database can surprise you.

Upvotes: 0

GMB
GMB

Reputation: 222482

It looks to me like you want to filter the content of table1 according to the address/zip tuples that are present in table2.

Here is one approach using exists and a correlated subquery:

select t1.*
from table1 t1
where exists (select 1 from table2 t2 where t2.address = t1.address and t2.zip = t1.zip)

For performance, consider an index on table2(address, zip).

You can also use in, which sometimes brings surprising performance benefits:

select t1.*
from table1 t1
where (zip, address) in (select zip, address from table2)

Upvotes: 1

Related Questions