When it comes to JOINS, there are 3 flavors in MySQL: LEFT, RIGHT, and INNER.
This is an example of how to join two tables a and b: SELECT (some columns) FROM a XXX JOIN b ON (some logical comparison) WHERE (some overall condition) Behind the scenes, the data engine creates a virtual table that consists of every column from each table that participates in the query. In this case it will have all of the columns of a and all of the columns of b. Which columns in that virtual table will be full of data and which columns will be full of NULLs or data depends on what you put in for XXX. If XXX were "LEFT", all of the a columns would be full of a data (every row in the a table would be listed) but the b columns would only have data where the ON condition was satisfied. All of the b columns in all of the other rows where the ON condition was not satisfied would be full of NULL values. The opposite occurs with a RIGHT JOIN. All of the b columns would be full of data (every row in the table would be listed) but the a columns would either have NULLs or data depending on if those rows satisfied the ON clause. That's why using a LEFT JOIN or a RIGHT JOIN and looking for null values in the WHERE clause can find rows between tables that DO NOT match certain criteria (as specified in the ON clause) Yes, you were right when you said that INNER JOIN will ONLY return rows from either table if those rows satisfy the ON condition. One thing that surprises some people is that joining tables can (and often does) create more records than either table contains. By example: table a has 100 record, table b has 10 records but you get back 106 records from this query: SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id What happened was that there was at least 1 record in a with multiple matches to records in b. When the database engine builds its virtual table, each combination of a data and b data that satisfy the ON condition will be listed in that table. That's why this query (keeping table sizes the same) will return 1000 rows of data: SELECT a.*, b.* FROM a INNER JOIN b (what kind of join you use in this case is irrelevant as there is no ON clause to make a difference) If you actually ran this query you would see each row of a paired up with each row of b. This is what is known as a "Cartesian product" or "cross product" of your two tables. Cartesian products are not something I work with often (I actually try my best to avoid them through well crafted ON clauses), but it does have its uses at times. The best way to get a good grip on how all the different JOIN clauses work is to just try them all out and notice the differences. Have Fun! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Giulio <[EMAIL PROTECTED]> wrote on 07/29/2004 11:39:10 AM: > Brilliant ! > thank you so much! > > only one more question: INNER JOIN is not a good choose because it > lists only the record with a matching ON clause, and this is the only > difference between LEFT and INNER, correct? > > thanks again, > > Giulio > > > Il giorno 29/lug/04, alle 16:03, [EMAIL PROTECTED] ha scritto: > > > Assume you want to check the availability of a room between two dates. > > You > > only want to get the rooms that are available for the entire time from > > @startdate to @enddate > > > > set @startdate='some_start_date', @enddate='some_end_date' > > > > SELECT r.room_number, r.room_type > > FROM rooms r > > LEFT JOIN reservations rv > > on rv.room_ID = r.id > > AND rv.startdate <= @enddate > > AND rv.enddate >= @startdate > > WHERE rv.id is null > > NOTE: the time portion of @startdate should be 00:00:00 on the first > > day > > and the time portion of @enddate should be set to 23:59:59 for the last > > day of the window you are interested in. > > > > How this works: > > The ON conditions of the JOIN of the table rooms to the table > > reservations > > identifies any reservation that covers any portion of the window you > > are > > interested in (draw it out with a number line, you will see the logic > > works) > > But because it's a LEFT JOIN (not an INNER JOIN) it lists all of the > > rooms > > regardless of whether or not there is a reservation. So, for those > > rows > > with room information but without reservation information to match our > > ON > > conditions, all of the columns that would normally have reservation > > data > > in them will have null values. We only want those unmatched rows so > > that's > > why we wrote the WHERE to return only those where the rv.id is null > > but we > > could have checked for NULL in any reservation column. > > > > Yours, > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > Giulio <[EMAIL PROTECTED]> wrote on 07/29/2004 09:18:50 AM: > > > >> Hi all, > >> I'm using two tables for handling a little room reservations system: > >> > >> a rooms table: > >> > >> CREATE TABLE `rooms` ( > >> `id` int(11) NOT NULL auto_increment, > >> `room_number` int(3) default NULL, > >> `room_type` char(1) default NULL, > >> PRIMARY KEY (`id`) > >> ) TYPE=MyISAM; > >> > >> a reservations table: > >> > >> CREATE TABLE `reservations` ( > >> `id` int(11) NOT NULL auto_increment, > >> `room_id` int(11) default NULL, > >> `date_from` date default NULL, > >> `date_to` date default NULL, > >> PRIMARY KEY (`id`) > >> ) TYPE=MyISAM; > >> > >> I have quite clear the join syntax (using for the the ON clause > >> rooms.id and reservations.room_id fields ) to list all the rooms > >> reserved on a given date interval, but I can't figure out the query to > >> list all the rooms of a given type (room_type) that results FREE ( not > >> reserved ) on the given date interval. > >> > >> thanx for your help, > >> > >> Giulio > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/[EMAIL PROTECTED] > >> > Cantoberon Multimedia srl > http://www.cantoberon.it > Tel. 06 39737052 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
