#1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    141

    Is joining to a view the same as joining to a subselect?


    Is joining to a view the same a joining to subselect?

    To provide a little background to why I'm asking, I'll be allowing resources in a system to be tagged, but a tag can only be used with a single resources type.

    For example, resources might be apples and oranges. The tags table would look like:
    tags[tag_id, tag_name, fruit_type]

    In this way, a tag can be defined for Apples, or Oranges, but not both.

    To ensure joins to this table are correct (i.e. Apples cannot be joined to an Orange tag), I was planning to create a `appleTags` and `orangeTags` views.

    Obviously, if joining to a view IS the same as joining to a subselect, then it would be better to join directly to the `tags` table and include fruit_type on the join.
    Ooh, they have the Internet on computers now!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    for your purposes, it's the same

    obviously a view has other attributes (such as you can grant/deny it to certain users, hide certain columns from it, etc.)

    would it be better to join to appletags or orangetags instead of a subquery? depends on how you measure "better"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    141
    Originally Posted by r937
    for your purposes, it's the same

    obviously a view has other attributes (such as you can grant/deny it to certain users, hide certain columns from it, etc.)

    would it be better to join to appletags or orangetags instead of a subquery? depends on how you measure "better"

    For reference, I wouldn't use a subquery to do this. If joining to a view would be the same as a subquery, I would not join to the view.

    Note that I'm referring to MySQL, not MariaDB, so I would avoid producing a query the equivalent of subquery for reasons of performance.
    Ooh, they have the Internet on computers now!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    Originally Posted by DrWorm
    For reference, I wouldn't use a subquery to do this. If joining to a view would be the same as a subquery, I would not join to the view.
    you wouldn't use a subquery, and you wouldn't join to the view...

    ... so, what would you do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    141
    Originally Posted by r937
    you wouldn't use a subquery, and you wouldn't join to the view...

    ... so, what would you do?
    Join directly to the table. I.e:
    Code:
     FROM apple_has_tags a JOIN tags t ON (t.id = a.tag_id ANd t.fruit='apple')
    as opposed to
    Code:
     FROM apple_has_tags a JOIN (SELECT * FROM tags WHERE fruit='apple') t ON (t.id = a.tag_id)
    But if I used a view:
    Code:
     FROM apple_jas_tags a JOIN appleTags_view t ON (t.id = a.tag_id)
    But if these last two options were essentially the same (in terms of performance), then I would use the first.
    Last edited by DrWorm; December 2nd, 2012 at 05:41 AM.
    Ooh, they have the Internet on computers now!

IMN logo majestic logo threadwatch logo seochat tools logo