Cartesian product of two tables not working as expected in MySQL but fine with SQlitee? 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-&gt;{id,name,dep})
table 2 (4 entries): (dep-&gt;{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
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&gt;  use test
Database changed
mysql&gt; CREATE TABLE emp
    -&gt;  (
    -&gt;      id int primary key,
    -&gt;      name varchar(20),
    -&gt;      did int
    -&gt;     );
Query OK, 0 rows affected (0.45 sec)

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

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

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


I ran your query without aliases

mysql&gt; 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&gt; 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&gt;


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

mysql&gt; 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&gt;


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.