1

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple

table 1 (5 entries): (emp->{id,name,dep})
table 2 (4 entries): (dep->{id,name})

I was supposed to write one inner join on dep column which worked with SQLite, but when I tried the same query with MySQL it failed. So in order to investigate, I just checked the Cartesian product of two tables for both the databases.

I tried this query

select * from dep a,emp b

in both databases.

In SQLite, it correctly return 20 entries with 5 columns in it. But in MySQL, it is returning correctly 20 entries but only 3 columns in it (id,name,did). So this explains why the join was breaking earlier.

I can't understand how can this Cartesian product be incorrect? Can someone please explain to me, what is going wrong in MySQL case?

MySQL fiddle

SQLite fiddle

  • 1
    Why don't you specify the columns you want? – Mat Aug 9 '14 at 11:51
  • Can you show us the query that was "breaking"? – ypercubeᵀᴹ Aug 9 '14 at 17:09
  • From what I know, MySQL returns 5 columns, too. Some intermediate layers that you may be using (PHP, SQL-Fiddle, whatever) have issues though when the result set has 2 columns with the same name (alias). That explains why you see 3 columns. Fix your SELECT list with explicit column names and alias them so all 5 columns in the result have different names. – ypercubeᵀᴹ Aug 9 '14 at 17:18
2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  use test
Database changed
mysql> CREATE TABLE emp
    ->  (
    ->      id int primary key,
    ->      name varchar(20),
    ->      did int
    ->     );
Query OK, 0 rows affected (0.45 sec)

mysql>
mysql>
mysql>
mysql> CREATE TABLE dep
    ->  (
    ->      id int primary key,
    ->      name varchar(20)
    ->     );
Query OK, 0 rows affected (0.35 sec)

mysql>
mysql> INSERT INTO dep
    -> VALUES
    -> (1,'lakers'),
    -> (2,'spurs'),
    -> (3,'sixers'),
    -> (4,'pacers'),
    -> (5,'warriors')
    ->
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO emp
    -> VALUES
    -> (1,'rohit',1),
    -> (2,'amit',2),
    -> (3,'haris',3),
    -> (4,'eti',4)
    -> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

I ran your query without aliases

mysql> select * from dep,emp;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.03 sec)

I ran your query as you gave it, with aliases

mysql> select * from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I ran your query with aliases in the SELECT clause as well as the FROM clause

mysql> select a.*,b.* from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I just ran these in MySQL 5.6.15 on my Windows 8 Laptop. It works fine with and without aliases. It might be SQL Fiddle that has the problem in this instance with a MySQL Cartesian Product.

  • Indeed, this is an issue between SQL-Fiddle and MySQL, when two columns in the result have the same alias. Nothing to do with the cross product, only a side effect of using select * with more than one tables. See Fiddle – ypercubeᵀᴹ Aug 9 '14 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.