CODEWITHSUNDEEP
sqlcross-product
Steve
Steve

Reputation: 1995

query: cross product instead of join

I have two tables that I would like to join but I am getting an error from MySQL

Table: books
bookTagNum ShelfTagNum
book1      1
book2      2
book3      2

Table: shelf
shelfNum   shelfTagNum
1          shelf1
2          shelf2

I want my results to be:

bookTagNum ShelfTagNum shelfNum
book1      shelf1           1
book2      shelf2           2
book3      shelf2           2

but instead I am also getting an extra result:

book1      shelf2           2

I think my query is doing a cross product instead of a join:

SELECT `books`.`bookTagNum` , `books`.`shelfNum` , `shelf`.`shelfTagNum` , `books`.`title`
FROM books, shelf
where `books`.`shelfNum`=`books`.`shelfNum`
ORDER BY `shelf`.`shelfTagNum` ASC
LIMIT 0 , 30

What am I doing wrong?

Upvotes: 1

Views: 10191

Answers (6)

Spechal
Spechal

Reputation: 2716

As others have mentioned, the problem you faced was with your ON condition. To specifically answer your question:

In MySQL, if you omit a JOIN, an INNER JOIN/CROSS JOIN is used. For other databases, it is different. For example, PostgreSQL uses a CROSS JOIN, not an INNER JOIN.

Re: http://dev.mysql.com/doc/refman/5.7/en/join.html

"In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise."

Upvotes: 0

Blair Conrad
Blair Conrad

Reputation: 242140

I think you want

where `books`.`shelfTagNum`=`shelf`.`shelfNum`

In order to match rows from the books and shelf tables, you need to have terms from each in your where clause - otherwise, you're just performing a no-operation check on the rows of books, since every row's shelfNum will be equal to its shelfNum.

As @fixme.myopenid.com suggests, you could also go the explicit JOIN route, but it's not necessary.

Upvotes: 9

Robert Paulson
Robert Paulson

Reputation: 18081

FYI: If you rewrite your names to be consistent, things get a lot easier to read.

Table 1: Book
BookID     ShelfID  BookName
1          1        book1
2          2        book2
3          2        book3

Table 2: Shelf
ShelfID    ShelfName
1          shelf1
2          shelf2

now, a query to extract books to shelves is

SELECT 
 b.BookName,
 s.ShelfName
FROM
 Book b
JOIN Shelf s ON s.ShelfID = b.ShelfID

To answer the original question:

> where `books`.`shelfNum`=`books`.`shelfNum`
>        ^^^^^--------------^^^^^------------- books repeated - this is an error

the WHERE clause, as written, does nothing, and because your where clause isn't limiting any rows, you are indeed getting the cross product.

Upvotes: 4

Tor Haugen
Tor Haugen

Reputation: 19637

Check your SQL. Your where clause cannot possibly be books.shelfNum=books.shelfNum

And what are all those single quotes for?

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103155

Try this:

SELECT `books`.`bookTagNum` , `books`.`shelfNum` , `shelf`.`shelfTagNum` , 
   `books`.`title`
FROM books, shelf
where `books`.`shelftagNum`=`shelf`.`shelfNum`
ORDER BY `shelf`.`shelfTagNum` ASC
LIMIT 0 , 30

Because the implicit JOIN condition was not properly stated the result was a cross product.

Upvotes: 0

Tor Haugen
Tor Haugen

Reputation: 19637

if you want to be sure you're doing a join instead of a cross product, you should state it explicitly in the SQL, thus:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books INNER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(which will return only those rows which exist in both tables), or:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books LEFT OUTER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(which will return all rows from books), or:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books RIGHT OUTER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(which will return all rows from shelf)

Upvotes: 5

Related Questions