Geek asks and answers

Left and inner difference in connection ... once and for all

I know that many topics have been created here and on the Internet on this topic. But I really can't get the final point about the difference between the two statements! I mean, trying and trying to achieve all the results that I need, with my requests, but I really do not have full control over the knife!

I consider myself a very good programmer and very good SQL-ista, and I'm a little ashamed of that ...

Here is an example:

  • I have a table with website pages ("web_page")
  • table with categories ("category").
  • a category can contain one or more pages, but not vice versa
  • a category may contain NO pages in general
  • the page may or may not be visible on the website.

So, if I want to show all categories and their pages, I mean both categories with and without pages, I should do something like this:

FROM category
LEFT JOIN web_page ON ( web_page.category_id = category.category_id AND web_page.active = "Y" )

So, if there are no pages in the category, I will see in the declaration of this category the value NULL web_page_id.

But if I do this:

FROM category
LEFT JOIN web_page ON ( web_page.category_id = category.category_id )
...
WHERE web_page.active = "Y"...

I will choose only those categories that have at least one web page ... But WHY?

It was just an example ... I would like to find out this difference one day!

Thank.

+4
source share
3 answers

To make your request work as you planned, put a condition in the sentence ON:

FROM category
LEFT JOIN web_page ON web_page.category_id = category 
   and web_page.active = "Y"

, ( , ), WHERE . - ( -), - null, (, "Y") null - false, , , .

, ON, , active = "Y", , null.

: " - ( )"

, " "... mysql, , , , , , , mysql.

+4

, SQL :

  • FROM ( );
  • WHERE;
  • GROUP BY;
  • ( MySQL, );
  • ORDER BY.

, web_page.active='Y' . , , OUTER. , NULL .

+1

.

Consider a facet of the SQL language, which, when the criteria is specified in LEFT JOIN, is used when searching for records matching. When the criteria are specified in the WHERE clause below, it applies to all records - after the join. This has an unintended side effect of a change LEFT JOINon INNER JOIN, as you saw.

You can get around this by running the sentence WHEREas follows:

WHERE COALESCE(web_page.active,"Y") = "Y"

But this should not be the same result, so the right way to do this is to keep these criteria in the sentences ON JOIN.

0
source

More articles:


All Articles