MySQL Forums :: Newbie :: Difference between two SQL queries


Advanced Search

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 Shivam Kapoor 07/16/2011 04:03AM
Re: Difference between two SQL queries laptop alias 07/16/2011 05:24AM
Re: Difference between two SQL queries Rick James 07/16/2011 02:15PM
Re: Difference between two SQL queries Shivam Kapoor 07/18/2011 01:47AM
Re: Difference between two SQL queries Rick James 07/18/2011 08:46AM
Re: Difference between two SQL queries Guelphdad Lake 07/18/2011 05:55AM
Re: Difference between two SQL queries laptop alias 07/18/2011 06:42AM
Re: Difference between two SQL queries Guelphdad Lake 07/18/2011 06:45AM
Re: Difference between two SQL queries laptop alias 07/18/2011 06:48AM


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.