Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4991

Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4993
Qustion 14: What will the following query return? – MySQL Question of the Day

Skip to content

Qustion 14: What will the following query return?

10. February 2010
By urs in mysql questions

1 Comment

 

select concat(v1.pronoun,' ',v2.verb,' ',v3.object, ' :-)' ) as sentence
  from
    (select 1 as id,'She' as pronoun union select 2 ,
    'I' union select 3 ,'He' ) v1
  left outer join
    (select 1 as id,'like' as verb union select 2 ,
    'likes' union select 3 ,'hates' ) v2
      on (v1.id > v2.id)
  left outer join
    (select 1 as id,'chocolade' as object union select 2 ,
    'playing' union select 3 ,'sql' ) v3
      on (v1.id < v3.id)
  having sentence is not null ;

  

a) She likes chocolade :-)
b) He hates playing :-)
c) I like sql :-)

 
[ Just for fun ]
 

Tags: , ,

Comment Feed

One Response

  1. urs — 10. February 2010 @ 20:00:17 —


     
    Result:

    +---------------+
| sentence      |
+---------------+
| I like sql :) |
+---------------+
1 row in set (0.03 sec)

     
    This query is a little unusual:
    v1, v2 and v3 are subqueries in the FROM-clause. They act like tables with 2 columns and 3 rows each.
     
    Because they are left joined, nulls are returned, which are then filtered out with the HAVING-clause.
    If one of the arguments in a CONCAT is null, then the CONCAT will return NULL.
    Usually HAVING is used together with GROUP BY, but it also works without.

     

You must be logged in to post a comment.