Semantics of SELECT D, E FROM A, B

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

SQL Server 2000 Forums

SQL Server Development (2000)

Semantics of SELECT D, E FROM A, B

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-21 : 15:35:41

I need to get Firstname, Coursename without any joining.

select Firstname, Coursename
from tablea, tableb
where UserID=@UserID and CourseID=@CourseID

the syntax: from tablea, tableb

Does this syntax imply any kind of joining or is it a way to retrieve data from two tables without joining?

OK, if there's an implied join, then what's a better construction of the select?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-21 : 15:44:51

There's no implied JOIN because there's no common condition specified between the two tables, even if there are columns in common between them. And if there is no relationship, then I don't think you can write the query any differently than what you have now (can't tell for sure unless you post the full DDL for both tables)

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-21 : 15:46:32

When you select rows from two table, you should always make an explicit join. With your query I guess you should write something like this :
select Firstname, Coursename
from tablea, tableb
where UserID=@UserID and CourseID=@CourseID
and tablea.userId=tableb.userId

Else, in many case you would get too many results.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-21 : 16:02:48

Well, that was my point: there IS no relationship between the tables. How do you know there's a UserID in tableB? It wasn't specified, and if there was, the query would throw an error because it would not be able to resolve which UserID column to use.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2003-01-21 : 16:12:00

Interesting, I thought this kind of situation implied a JOIN. If that is not the case, then obviously you will be much better off specifying a specific JOIN condition (if possible) as this will be much faster.

***************************************
Death must absolutely come to enemies of the code!

Edited by - royv on 01/21/2003 16:12:24

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-21 : 16:26:44

I guess I throught there might be an implied cross join.

There's no relationship between the two tables, so I don't want any join at all. Table A has users, table B has courses. No relationship. Users can take any number of courses.

What's *sort of* odd about the syntax is the WHERE condition1 and condition2 makes it look like a join is present.

There's an alternate syntax

SELECT (select firstname from users where userid=@userID) as firstname,
(select coursename from courses where CourseID=@CourseID) as CourseName

But somehow this format doesn't look as clean to me.

Sam

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-21 : 16:34:00

it is an implied cross join

SELECT * FROM Users, Courses
SELECT * FROM Users CROSS JOIN Courses

same thing ... just looks purdy with something like

SELECT * FROM Users ... lots of joins ... CROSS JOIN Courses

rather than

SELECT * FROM Users ... lots of joins ... , Courses

same thing though ... I do it a lot for making sure I have at least so many records

SELECT Name, Years.Value AS Year, SUM(CASE WHEN Resources.Year = Years.Value AND Resources.Month = Months.Value THEN Resources.Value ELSE 0 END) AS January, ... more case statements for each month ... FROM Resources CROSS JOIN dbo.Range(1, 12) AS Months CROSS JOIN dbo.Range(2000, 2003) AS Years GROUP BY Resources.Name, Years.Value

stuff like that ... looks pretty and you can say you are using one of the most complex structures that isn't used a lot, a cross join... even though all old school FROM statements were cross joins that were turned into inner or outer joins in the where clause...

maybe I just eat too many mushrooms at work...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-21 : 16:35:16

quote:
What's *sort of* odd about the syntax is the WHERE condition1 and condition2 makes it look like a join is present.

It doesn't look that way to me. If you use WHERE syntax to join tables, you should stop and use the ANSI JOIN syntax only. That will lead to less confusion (if there's no JOIN clause, then there's no JOIN) and more logically sound queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources