Why so many queries? (7 instead of 1?)


#1

Why does this code return the same thing so many times?

SELECT albums.name, albums.year, artists.name FROM albums, artists;

Query Results
name year name
A Hard Days Night 1964 The Beatles
A Hard Days Night 1964 Elvis Presley
A Hard Days Night 1964 Michael Jackson
A Hard Days Night 1964 Madonna
A Hard Days Night 1964 Elton John
A Hard Days Night 1964 Led Zeppelin
A Hard Days Night 1964 Pink Floyd
Elvis Presley 1956 The Beatles
Elvis Presley 1956 Elvis Presley
Elvis Presley 1956 Michael Jackson
Elvis Presley 1956 Madonna
Elvis Presley 1956 Elton John
Elvis Presley 1956 Led Zeppelin
Elvis Presley 1956 Pink Floyd
1989 2014 The Beatles
1989 2014 Elvis Presley
1989 2014 Michael Jackson
1989 2014 Madonna
1989 2014 Elton John
1989 2014 Led Zeppelin
1989 2014 Pink Floyd
Yellow Submarine 1968 The Beatles
Yellow Submarine 1968 Elvis Presley
Yellow Submarine 1968 Michael Jackson
Yellow Submarine 1968 Madonna
Yellow Submarine 1968 Elton John
Yellow Submarine 1968 Led Zeppelin
Yellow Submarine 1968 Pink Floyd
Hey Jude 1970 The Beatles
Hey Jude 1970 Elvis Presley
Hey Jude 1970 Michael Jackson
Hey Jude 1970 Madonna
Hey Jude 1970 Elton John
Hey Jude 1970 Led Zeppelin
Hey Jude 1970 Pink Floyd
Like a Virgin 1984 The Beatles
Like a Virgin 1984 Elvis Presley
Like a Virgin 1984 Michael Jackson
Like a Virgin 1984 Madonna
Like a Virgin 1984 Elton John
Like a Virgin 1984 Led Zeppelin
Like a Virgin 1984 Pink Floyd
From Elvis in Memphis 1969 The Beatles
From Elvis in Memphis 1969 Elvis Presley
From Elvis in Memphis 1969 Michael Jackson
From Elvis in Memphis 1969 Madonna
From Elvis in Memphis 1969 Elton John
From Elvis in Memphis 1969 Led Zeppelin
From Elvis in Memphis 1969 Pink Floyd
Bad 1987 The Beatles
Bad 1987 Elvis Presley
Bad 1987 Michael Jackson
Bad 1987 Madonna
Bad 1987 Elton John
Bad 1987 Led Zeppelin
Bad 1987 Pink Floyd
Elton John 1970 The Beatles
Elton John 1970 Elvis Presley
Elton John 1970 Michael Jackson
Elton John 1970 Madonna
Elton John 1970 Elton John
Elton John 1970 Led Zeppelin
Elton John 1970 Pink Floyd
Like a Prayer 1989 The Beatles
Like a Prayer 1989 Elvis Presley
Like a Prayer 1989 Michael Jackson
Like a Prayer 1989 Madonna
Like a Prayer 1989 Elton John
Like a Prayer 1989 Led Zeppelin
Like a Prayer 1989 Pink Floyd
The Dark Side of the Moon 1973 The Beatles
The Dark Side of the Moon 1973 Elvis Presley
The Dark Side of the Moon 1973 Michael Jackson
The Dark Side of the Moon 1973 Madonna
The Dark Side of the Moon 1973 Elton John
The Dark Side of the Moon 1973 Led Zeppelin
The Dark Side of the Moon 1973 Pink Floyd
Thriller 1982 The Beatles
Thriller 1982 Elvis Presley
Thriller 1982 Michael Jackson
Thriller 1982 Madonna
Thriller 1982 Elton John
Thriller 1982 Led Zeppelin
Thriller 1982 Pink Floyd
Unorthodox Jukebox 2012 The Beatles
Unorthodox Jukebox 2012 Elvis Presley
Unorthodox Jukebox 2012 Michael Jackson
Unorthodox Jukebox 2012 Madonna
Unorthodox Jukebox 2012 Elton John
Unorthodox Jukebox 2012 Led Zeppelin
Unorthodox Jukebox 2012 Pink Floyd
The Wall 1979 The Beatles
The Wall 1979 Elvis Presley
The Wall 1979 Michael Jackson
The Wall 1979 Madonna
The Wall 1979 Elton John
The Wall 1979 Led Zeppelin
The Wall 1979 Pink Floyd

It gives me 7 results of each album, but I don't see why?

Thanks


#2

Hey @midp,

think I got the mistake: you forgot to define on which columns to join the two tables.
Try

SELECT
albums.name
,albums.year
,artists.name
FROM
albums
JOIN artists ON
albums.artist_id = artists.id;

this may work as well

SELECT
albums.name
,albums.year
,artists.name
FROM albums, artists
WHERE albums.artist_id = artists.id;


#3

You've done a "cross join". Basically you've created every single combination of the contents of both tables. So for each album you've combined it with every possible artist. Visit w3resource's Cross Join if you are still confused.