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
Question 75: What’s the result of the query? – MySQL Question of the Day

Skip to content

Question 75: What’s the result of the query?

27. June 2010
By plogi in mysql questions

1 Comment

select a b from (select 1 a) b, (select 1 b union select 2) a;

a)
+---+
| a |
+---+
| 1 |
| 1 |
+---+

b)
+---+
| b |
+---+
| 1 |
| 1 |
+---+

c)
+---+
| b |
+---+
| 2 |
| 2 |
+---+

d)
+---+
| b |
+---+
| 1 |
+---+

[Just for fun]

Tags: , ,

Comment Feed

One Response

  1. plogi — 27. June 2010 @ 11:50:47 —


    Answer b is correct. But why?

    This query has two subqueries in the FROM-clause: a and b.
    Subquery b results in 

    +---+
| a |
+---+
| 1 |
+---+


    And a results in 

    +---+
| b |
+---+
| 1 |
| 2 |
+---+


    We select column a from subquery b and give it the alias b.
    This explains the ‘b’ as the column name in the result.
    The subqueries are joined together without any restriction:
    There is no on-clause or where-clause. That means, the result has 1 * 2 rows (cartesian product join).
    This query is syntactically a bit confusing, but I hope it helps understanding subqueries, aliases and joins.

    MariaDB [mysql]> select a b from (select 1 a) b, (select 1 b union select 2) a;
a)
+---+
| b |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)

You must be logged in to post a comment.