Re: Joins over the equality sign

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 19:39:08 +0200

What I'm (trying :-) ) to say is: Consider below::

SELECT ...
FROM tbl1, tbl2
WHERE tbl1.col = tbl2.col

Logically the FROM clause is processed first. This gives us a Cartesian product, right? After that you filter
away a number of rows in the WHERE clause. This is, used correctly, what we usually refer to as a "join". I
fail to see how above syntax can be removed from the SQL language,. quire simply.

I agree that if you *want* a Cartesian product, then use the new syntax and CROSS JOIN. I'm just reasoning
that it would be difficult to disallow the old syntax. Unless you remove to specify more than one table in the
FROM clause (without using JOIN), but I guess that this would be a too deep change to do for both ANSI and MS. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam Machanic" <amachanic@air-worldwide.nospamallowed.com> wrote in message
news:eD2a4SAGEHA.2052@TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
> message news:OZj1xMAGEHA.3984@TK2MSFTNGP10.phx.gbl...
> > The old-style inner join syntax will not be removed from ANSI or SQL, as
> there is nothing to prevent us from
> > doing a Cartesian product and then a restriction (which is what the old
> style really does, logically).
>
>     I'm not sure I understand what you mean; doesn't CROSS JOIN provide us
> with that capability?
>
>

Relevant Pages

  • Re: Statement parameter in Mailmerge.OpenDataSource
    ... You can certainly pass an SQL SELECT statement with a WHERE clause. ... the syntax right can be difficult, ... You may be able to find out more about that in SQL Server Books Online. ... > What i'm trying to do is, passing a statement calling a table-valued ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQL syntax over a network
    ... > SQL you mean copied from the SQL view of a Query, ... > Syntax error in FROM clause ... But that doesn't match the syntax that you originally said it did; ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: MSACCESS 2003 CREATE PROC
    ... The query is incorrect in the GROUP BY clause. ... syntax instead of JET's syntax (Using an aggregate alias in an ... CurrentProject.Connection.Execute SQL ...
    (comp.databases.ms-access)
  • Re: Programming for a Dynamic Where clause
    ... diffcult to check the SQL syntax around the dates. ... When you're done building the SQL string use debug.print and pate the ... Tim. ... >> clause is ...
    (microsoft.public.excel.programming)
  • SQL Server 7 SP4 - Error 8630
    ... We've managed to get the SQL working, ... The 1st view was a cartesian product of all the ... However if you change the order in the from clause in the ... >(The query works OK in SQL Server 2000). ...
    (microsoft.public.sqlserver.server)