Announcement

Announcement Module
Collapse
No announcement yet.

Inner joining big table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Inner joining big table

    Is there a difference if the table being joined is a big table? (compared to the other way around?)

    select p.*, f.notes FROM featured f INNER JOIN items p ON f.item_id = p.id WHERE f.type = 0 ORDER BY f.created_at DESC

    select p.*, f.notes FROM items p INNER JOIN featured f ON (f.item_id = p.id AND f.type = 0) WHERE p.featured = 1 ORDER BY f.created_at DESC

    select p.*, f.notes FROM items p INNER JOIN featured f ON (f.id = p.featured) WHERE p.featured != 0 ORDER BY f.created_at DESC

    Which of these queries would be the best? Is the WHERE clause only executed after the INNER JOIN has been done?

    Sorry for these "noob" questions )

  • #2
    For Inner join it does not matter. I would probably use conceptual schema to split ON/WHERE clause - ie put foreign key in ON and extra filters in WHERE.

    For LEFT JOIN it is different as as ON clause generates NULL rows in joined table if it is not matches, while WHERE clause works as normally filtering out rows which do not match it.

    Comment

    Working...
    X