MySQL Forums
Forum List  »  Newbie

Difference between two SQL queries
Posted by: Shivam Kapoor
Date: July 16, 2011 04:03AM

I have created two tables named 'pet' and 'event' as shown below :

Event Table
+----------+------------+----------+-----------------------------+
| name | date | type | remark |
+----------+------------+----------+-----------------------------+
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male |
| Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male |
| Buffy | 1994-06-19 | litter | 3 puppies, 3 female |
| Chirpy | 1994-03-21 | vet | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Browser | 1991-10-12 | kennel | |
| Fang | 1991-10-12 | kennel | |
| Fang | 1998-08-28 | birthday | gave him new chew toy |
| Claws | 1998-03-17 | birthday | gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | first birthday |
+----------+------------+----------+-----------------------------+

Pet Table
+----------+--------+----------+------+------------+------------+
| name | owner | speicies | sex | birth | death |
+----------+--------+----------+------+------------+------------+
| fluffy | harold | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| Buffy | harold | dog | f | 1989-05-13 | NULL |
| fang | benny | dog | m | 1990-08-27 | NULL |
| browser | diana | dog | m | 1979-08-31 | 1995-07-29 |
| chipy | gwen | bird | f | 1998-09-11 | NULL |
| whistler | gwen | bird | | 1997-12-09 | NULL |
| slim | benny | snake | m | 1996-04-29 | NULL |
+----------+--------+----------+------+------------+------------+


I am executing two queries which return the same results as following -


+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+

and the queries are -

1. mysql> SELECT pet.name, ((YEAR(date)-YEAR(birth))-(RIGHT(date,5)<RIGHT(birth,5))
) AS age, remark
-> FROM pet, event
-> WHERE pet.name=event.name and type='litter';

2. mysql> SELECT pet.name, ((YEAR(date)-YEAR(birth))-(RIGHT(date,5)<RIGHT(birth,5))
) AS age, remark
-> FROM pet INNER JOIN event
-> WHERE pet.name=event.name and type='litter';


I have a question about the from clause. What is the difference between clauses 'FROM pet, event' and 'FROM pet INNER JOIN event'. I mean how they work internally??? I have studied about cartesian products of two tables in relational algebra which is used in order to derive such results. I am curious to know how cartesian product is related to these 'from' clauses.

In the anticipation of reply. Thanks in advance.

Options: ReplyQuote


Subject
Written By
Posted
Difference between two SQL queries
July 16, 2011 04:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.