MySQL Self Join Table

Can you help me understand JOIN a bit more

SELECT *
FROM Weather AS yesterday 
JOIN Weather AS today;

Why is the output of this starts with
id
4
3
2
1

SELECT *
FROM Weather AS yesterday 
JOIN Weather AS today;

Why is the output of this starts with
id
4
3
2
1

6 Replies

Dev_zxcOP•3mo ago

My expected output is the tables will literally join each other with same rows like id recordDate Temp id recordDate Temp 1 2015-01-01 10 1 2015-01-01 10 2 2015-01-02 25 2 2015-01-02 25 etc oh you need to use ON then link the same ID to output my expected output xD but can you tell me why it does that output when there's no condition?

ἔρως•3mo ago

if you want that, you need to select the data twice something like this:

select * from (
    select * from dual
), (
    select * from dual
)

select * from (
    select * from dual
), (
    select * from dual
)

if you notice, you didnt specify which column to join, so, it is going to join all the data of all the columns with all the data of the table in the join

Dev_zxcOP•3mo ago

Can you explain to me why it output this when i run this query only? SELECT * FROM Weather AS yesterday JOIN Weather AS today;

ἔρως•3mo ago

^ here but i might be wrong about the "all columns", as it seems to be doing it just for the primary key but i can't see the rest of the results, so

Dev_zxcOP•3mo ago

Thanks! @ἔρως

ἔρως•3mo ago

you're welcome

Gaming

Programming

MySQL Self Join Table

Did you find this page helpful?