Whats the difference between these?

[Zych]

I am still a noob when it comes to SQL statements. I have had some help here from a few of you and hopefully you can explain the difference between these SQL statements.

SQL Statement 1.

SELECT * FROM Table1, Table2
Where Table1.ID = Table2.ID;

SQL Statement 2.

SELECT * FROM Table1
WHERE ID IN(SELECT Table2.ID FROM Table2);

For some reason SQL statement 2 does not work on the MySQL system I am using. When I try SQL Statement 1 using MySQL Front it seems to lock up. I think this is due to needing more RAM. (Table1 has 700,000 records, Table2 has 400,000 records.) I am going to try and upgrade the RAM but I want to make sure I have written SQL statement 1 to give me the same info as statement 2. Any other rewrites are also welcome.

Thanks,

Zych

 

[swampBoogie]

The queries will return the same number of records if there is only one instance of each matching id in both tables.

Using these

select table1.* from table1,table2
Where Table1.ID = Table2.ID

select * from table1 where id in (
select id from table2 )

and having this data

table1:

ID ! t1
----------
1 ! some
2 ! other

table2:

ID
---
1
2


The result of both queries would be

ID ! t1
----------
1 ! some
2 ! other


If instead table2 contains

table2:

ID
---
1
2
2

the result with the first query would be


ID ! t1
----------
1 ! some
2 ! other
2 ! other

whereas the result of the second query would be the same as in the first example. That is, IN is more of a check that there is at least one matching row in the subquery whereas a join will combine all matching rows.

 

[Zych]

Thank you. That is good to know. In my case I have the ID set to unique in both tables so there can only be one match.

Thanks for clearing this up for me.

- Zych