Mixing Ansi 1992 Joins and Commas in a Query

Mixing ANSI 1992 JOINs and COMMAs in a query

It seems your requirement is to join members table but you are joining with telephone table. just change their order.

SELECT 
    `m`.*,
    `t`.* 
FROM
    `memebers` AS `m` 
    JOIN `telephone` AS `t` 
    JOIN `memeberFunctions` AS `mf` 
        ON `m`.`id` = `mf`.`memeber` 
        AND `mf`.`function` = 32 
    JOIN `mitgliedTelephone` AS `mt` 
        ON `m`.`id` = `mt`.`memeber`;

Hope this helps you. Thank you!!

Mixing explicit and implicit joins

If I remember correctly, explicit joins are being processed before implicit joins, therefore - t2. is not available yet.

Solution: Avoid the use of implicit join syntax, and use the proper syntax of joins , just like the second part of your query.

Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

I also try to evangelize the SQL-92 syntax. Sixteen years after it was approved, it's about time people start using it! And all brands of SQL database now support it, so there's no reason to continue to use the nonstandard (+) Oracle syntax or *= Microsoft/Sybase syntax.

As for why it's so hard to break the developer community of the SQL-89 habit, I can only assume that there's a large "base of the pyramid" of programmers who code by copy & paste, using ancient examples from books, magazine articles, or another code base, and these people don't learn new syntax abstractly. Some people pattern-match, and some people learn by rote.

I am gradually seeing people using SQL-92 syntax more frequently than I used to, though. I've been answering SQL questions online since 1994.

selecting from multiple tables vs JOIN

Precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN. That's why when you mix comma with other join table operators [Unknown column 'col_name' in 'on clause'] error occur. Same query will work if you specify the cross join ( to get a Cartesian product of the first two tables) instead of commas, because then in the from clause the table operators will be evaluated from left to right:

SELECT 
    fixture.*
FROM
    sport_team_player AS team_player
    cross join sport_team AS team 
    INNER JOIN sport_fixture AS fixture 
    ON (team_player.team_id = fixture.team1_id OR team_player.team_id = fixture.team2_id)
WHERE 
  team_player.team_id = team.team_id AND team_player.player_id = '16'

correct query design? cross joins driving ad-hoc reporting interface

Don't worry, historically Oracle used the first notation for inner joins but later on adopted ANSI SQL standards.

The results in terms of performance and returned recordsets are exactly the same, the implicit 'comma' joins are not crossing resultset but effectively integrating the WHERE filters. If you doubt it, run an EXPLAIN SELECT command for both queries and you will see the forcasted algorithms will be identical.

---

Expanding this answer you may notice in the future the analogous notation (+) in place of outer joins. This answer will also stand correct in that context.

The real issue comes when both notations (implicit and explicit joins) are mixed in the same query. This would be asking for trouble big time, but I doubt you find such a case in OBIEE.

SQL Query: why does adding another table change the results?

You have not specified a join condition, so what you're getting is the FULL CROSS JOIN which produces a row for every possible combination of rows in the base tables.

http://en.wikipedia.org/wiki/Join_(SQL)

I find that using the ANSI syntax for joins avoids this confusion. Don't just use the commas in the FROM clause... use actual JOIN clauses...

select cn.idConteudo, TIMESTAMPDIFF(SECOND, nl.dataInicio , nl.dataFim)
from navegacaolog nl
JOIN conteudoNo cn ON nl.idConteudoNo = cn.idConteudoNo
where TIMESTAMPDIFF(SECOND, nl.dataInicio , nl.dataFim) > 120

joining same table twice in a mysql query

It's a technique to retrieve the latest order status.

Because of

AND popsh.provider_order_product_status_history_id < popsh2.provider_order_product_status_history_id

and

AND popsh2.last_updated IS NULL

Only those order status that doesn't have any newer status are returned.

For a minimum set example, consider the following status history table:

id  status order_id last_updated
--------------------------------
1   A      X        1:00
2   B      X        2:00

The self join will result in:

id  status order_id last_updated    id  status order_id last_updated
--------------------------------    --------------------------------
1   A      X        1:00            2   B      X        2:00
2   B      X        2:00                NULL   NULL     NULL

The first row will be filtered out by the IS NULL condition, leaving only the second raw, which is the latest one.

For a 3-row case the self join result will be:

id  status order_id last_updated    id  status order_id last_updated
--------------------------------    --------------------------------
1   A      X        1:00            2   B      X        2:00
1   A      X        1:00            3   C      X        3:00
2   B      X        2:00            3   C      X        3:00
3   C      X        3:00                NULL   NULL     NULL

And only the last one will pass the IS NULL condition, leaving the latest one again.

It looks like an unnecessarily complicated way to do the job, but it actually works quite well as RDBMS engines do joins very efficiently.

BTW, as the query retrieves only order_id, the query is not useful as it is. I guess the OP omitted other fields in the select clause. It should be something like SELECT o.order_id, popsh.* FROM ...

Not sure I understand the Snowflake FROM syntax using comma and table(...)

Copying comments to an answer for closure (I'll delete this one if Gordon comes back):

• In SQL , is equivalent to CROSS JOIN.

That's ANSI-89 vs ANSI-92. https://stackoverflow.com/a/3918601/132438

(choose an explicit join if possible, if you have the choice)

---

Related Topics