2

Few months ago I was programming a simple application with som other guy in PHP. There we needed to preform a SELECT from multiple tables based on a userid and another value that you needed to get from the row that was selected by userid.

My first idea was to create multiple SELECTs and parse all the output in the PHP script (with all that mysql_num_rows() and similar functions for checking), but then the guy told me he'll do that. "Okay no problem!" I thought, just much more less for me to write. Well, what a surprise when i found out he did it with just one SQL statement:

SELECT
  d.uid AS uid, p.pasmo_cas AS pasmo, d.pasmo AS id_pasmo ...
FROM
  table_values AS d, sectors AS p
WHERE
  d.userid='$userid' and p.pasmo_id=d.pasmo
ORDER BY
  datum DESC, p.pasmo_id DESC

(shortened piece of the statement (...))

Mostly I need to know the differences between this method (is it the right way to do this?) and JOIN - when should I use which one?

Also any references to explanations and examples of these two would come in pretty handy (not from the MySQL ref though - I'm really a novice in this kind of stuff and it's written pretty roughly there.)

Community
  • 1
  • 1
Vultour
  • 373
  • 4
  • 15
  • 2
    Improve the title please. And try to limit yourself to one question per.. question. – Lightness Races in Orbit Jan 04 '12 at 01:07
  • Skipping over the irony of the first line being "Alright, let's get straight to the question"; by saying so, you precluded that! – Lightness Races in Orbit Jan 04 '12 at 01:08
  • This is a join, although it's an old-style syntax. In the future, please eliminate the unnecessary dramatization and noise, and ask a single question per question. Multiple questions make it harder to answer, and harder for a single answer to be selected as the right one. If you have three separate questions, and three people each address one of them, which one do you accept as correct? Sticking to a single question, and making it as clear as possible without all the cluttering commentary, helps you get an answer more quickly, and makes the question more useful to others in the future. Thanks. – Ken White Jan 04 '12 at 01:10
  • Well, i really didn't know how to name it. What's the secod method called? ... Old syntax? So I shouldn't be using it? – Vultour Jan 04 '12 at 01:10

3 Answers3

5

, notation was replaced in ANSI-92 standard, and so is in one sense now 20 years out of date.

Also, when doing OUTER JOINs and other more complex queries, the JOIN notation is much more explicit, readable, and (in my opinion) debuggable.

As a general principle, avoid , and use JOIN.


In terms of precedence, a JOIN's ON clause happens before the WHERE clause. This allows things like a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL to check for cases where there is NOT a matching row in b.

Using , notation is similar to processing the WHERE and ON conditions at the same time.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
1

This definitely looks like the ideal scenario for a join so you can avoid returning more data then you actually need. This: http://www.w3schools.com/sql/sql_join.asp or this: http://en.wikipedia.org/wiki/Join_(SQL) should help you get started with joins. I'm also happy to help you write the statement if you can give me a brief outline of the columns / data in each table (primarily I need two matching columns to join on).

Michael A
  • 9,480
  • 22
  • 70
  • 114
  • Although `,` is a Cartesian Product (CROSS JOIN) the existance of the WHERE clause means that it is *not* processed as one. In simple cases like this the same *plan* is generated whether or not `,` or `JOIN` is used. It's more a case of style and maintainability at this level. Though differing plans *can* be created at higher levels of complexity, `,` itself isn't *necessarily* worse in performance, if correctly written. – MatBailie Jan 04 '12 at 01:11
  • That project's finished and I'm not doing anything like this right now, but I just ran over it while looking trough some old projects. Thanks for the references :) – Vultour Jan 04 '12 at 01:15
  • @Dems My apologies I was overly brief as I felt if he was asking joins that adding too much detail would be daunting and I wanted to provide him with the fasted path for generating a good end result. – Michael A Jan 04 '12 at 01:18
0

The use of the WHERE clause is a valid approach, but as @Dems noted, has been superseded by the use of the JOINS syntax.

However, I would argue that in some cases, use of the WHERE clauses to achieve joins can be more readable and understandable than using JOINs.

You should make yourself familiar with both methods of joining tables.

Atonewell
  • 922
  • 4
  • 14
  • 1
    I'm currently running around screaming with my head under my arm and my eyes popped out. How can you recommend using this horrible notation? Argh! *[No, really, I am, I'm serious, honest]* – MatBailie Jan 04 '12 at 01:16
  • Could you provide an example of a situation where you would ever not use a join? I've never seen one and can't think of a situation where I'd ever use ',' over a join. Seems like pretty misleading advice. – Michael A Jan 04 '12 at 01:24