I have following 3 SQL tables:
mysql> select * FROM borrower;
+--------+------------+---------+--------------+
| cardno | name | address | phone |
+--------+------------+---------+--------------+
| 1 | A | nj,usa | 111-222-333 |
| 2 | B | NY,USA | 444-555-666 |
| 3 | C | nj,usa | 777-888-999 |
+--------+------------+---------+--------------+
3 rows in set (0.00 sec)
mysql> select * FROM bookloans;
+--------+----------+--------+------------+------------+
| bookid | branchid | cardno | dateout | duedate |
+--------+----------+--------+------------+------------+
| 1 | 1 | 1 | 2014-04-01 | 2014-08-01 |
| 2 | 1 | 1 | 2014-04-02 | 2014-08-02 |
| 3 | 2 | 2 | 2014-04-04 | 2014-07-05 |
+--------+----------+--------+------------+------------+
1 row in set (0.00 sec)
mysql> select * FROM books;
+--------+------------+---------+------
| bookid | title | publishername |
+--------+------------+---------+------
| 1 | Atitle | tmh |
| 2 | Btitle | tmh |
| 3 | Ctitle | tmh |
+--------+------------+---------+-----+
3 rows in set (0.00 sec)
mysql> select * FROM librarybranch;
+----------+------------+--------+
| branchid | branchname | address|
+--------+----------+--------+---+
| 1 | abc loc | nj,usa |
| 2 | def loc | NY,USA |
+----------+------------+---------+
2 rows in set (0.00 sec)
Now I want to execute a query that shows book title,borrowersname,borrowers address for each book loaned out by "def loc" branch whose duedate is today(2014-07-05). I came up with two possible queries:
1.Query based on joins:
select books.title,borrower.name,librarybranch.branchname from (( bookloans inner join
borrower on bookloans.cardno = borrower.cardno) inner join books on bookloans.bookid =
books.bookid inner join librarybranch on bookloans.branchid=librarybranch.branchid)
where duedate='2014-07-05' and branchname="def loc";
2.Query based on mutiple ands:
select b.title,r.name,r.address from books b,borrower r,bookloans bl,librarybranch lb
where lb.branchname='def loc' and lb.branchid = bl.branchid and bl.duedate='2014-07-05'
and bl.cardno=r.cardno and bl.bookid=b.bookid;
To me, query based on joins is most intuitive, we are creating a giant table with all the rows from different tables joined together and then querying this giant table based on columns from multiple tables. However I find it surprising that query based on multiple ands gives same result. My question is is option 2 above (one based on multiple ands) exactly equivalent to option 1 (doing inner joins). If not, what's the difference? They seem to give identical results in this case.
