Correct way to select from two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Correct way to select from two tables?

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2013-07-10 : 18:50:38

Back in the old days, I used to write select statements like this:


SELECT 
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'

However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

This leads to my problem.... I want to do a comparison of data between two tables but there is no common field in both tables with which to create a join. If I use the 'legacy' method of comma separated table names in the FROM clause, then it works perfectly. But I feel uncomfortable using this method if it is considered wrong or bad practice.

Anyone know what to do in this situation?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-10 : 19:16:09

How can you do a comparison if there's nothing in common? Using the comma separated way or the JOIN way is basically the same thing, just a different style of coding. If you didn't have anything in common with the old way, then it was doing a cartesian join which is not good.

Can you show us a data example of how a data comparison would work if nothing is in common?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-11 : 00:17:10

quote:
Originally posted by token

Back in the old days, I used to write select statements like this:

SELECT
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'

However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 00:59:12

quote:
Originally posted by bandi

quote:
Originally posted by token

Back in the old days, I used to write select statements like this:

SELECT
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'

However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'

--
Chandu

this is again a CROSS JOIN as Tara suggested

equivalent to


SELECT t1.columnA, t2,columnA
FROM table1 t1
CROSS JOIN table2 t2
WHERE t1.columnA = 'Some value'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-11 : 12:25:45

quote:
Originally posted by bandi

quote:
Originally posted by token

Back in the old days, I used to write select statements like this:

SELECT
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'

However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'

--
Chandu

He did ask for he correct way. ;) (just busting your chops)

token
Posting Yak Master

133 Posts

Posted - 2013-07-12 : 05:33:18

I used the CROSS JOIN as recommended and it works perfectly. Thank you!

I guess I need to start reading new SQL books again :(

FYI: One table had a list of particular places with geolocations. Another table had a different list of places with geolocations. I was comparing the distances between the various locations.

I can't join on a geolocation field (e.g. geography) hence no common field to join on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:06:02

you're welcome
yep...your scenario seems like you need to use CROSS JOIN itself.

read below to get idea on joins
http://w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources