CODEWITHSUNDEEP
mysqlsqljoin
neubert
neubert

Reputation: 16802

mysql grouping columns together by name

I was trying to create a demo to explain how JOINs and ran into some difficulty.

CREATE TABLE sample (
  id INT
);

INSERT INTO sample VALUES (1),(2),(3),(4);

That's my schema.

SELECT *
FROM sample AS s1
JOIN sample AS s2;

If you do that you'll get this:

1,1
1,2
1,3
1,4
2,1
2,2
...
4,3
4,4

At least that's what the JOIN sees. If you try to run it however you get one column who's values are equal to the first column. I don't want that - I want both columns.

I could do s1.*, s2.* instead of * however I want SQL to use it's own groupings. Reason being that LEFT and RIGHT JOINs don't concern themselves with how you're having the data appear in the output - they concern with themselves with the tables that were joined to the RIGHT and LEFT. And plus that doesn't really scale well. I just want to see what the JOIN sees.

Any ideas?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

This is a peculiarity of MySQL. Columns with the same name are "combined" into a single column on output. I don't believe this is ANSI-compliant behavior.

To demonstrate joins, try this instead:

select s1.id as id1, s2.id as id2
FROM sample s1 cross join
     sample s2;

This renames the columns, so both appear in the output. Also, I renamed the join to cross join. In MySQL, join can be used for either a regular join or a cartesian product, depending on the presence of an on clause. I find this behavior confusing. join should be an inner join. The cartesian product should use cross join.

This seems like a strangeness on SQLFiddle. When I run this code on SQL Fiddle for SQL Server, I get the same results as you. However, when I run this code on my local SQL Server, I get two columns:

CREATE TABLE sample (
  id INT
);

INSERT INTO #sample VALUES (1),(2),(3),(4);

SELECT *
FROM sample AS s1
cross JOIN sample AS s2;

This seems to be an issue with SQL Fiddle.

Upvotes: 1

Related Questions