-> MySQL Joins ‘the perfect example’


There are 3 kinds of join: INNER, LEFT OUTER, and RIGHT OUTER.

Each requires an ON clause to let the RDBMS know what fields to use joining the tables. For each join there are two tables: the left table and the right table. The syntax is as follows:

{left table} {INNER | LEFT | RIGHT} JOIN {right table} ON {join criteria}

An INNER JOIN returns only those rows from the left table having a matching row in the right table based on the join criteria.

A LEFT JOIN returns ALL rows from the left table even if no matching rows where found in the right table. Any values selected out of the right table will be null for those rows where no matching row is found in the right table.

A RIGHT JOIN works exactly the same as a left join but reversing the direction. So it would return all rows in the right table regardless of matching rows in the left table.

It is recommended that you not use right joins, as a query can always be rewritten to use left joins which tend to be more portable and easier to read.

With all of the joins, if there are multiple rows in one table that match one row in the other table, that row will get returned many times.

For example:
Table A
tid, name
1, 'Linux'
2, 'Debian'

Table B
fid, tid, message
1, 1, 'Very Cool'
2, 1, 'What an example'

Query 1:
SELECT a.name, b.message FROM a INNER JOIN b ON a.tid = b.tid
Result 1:
Linux, Very Cool
Linux, What an example

Query 2:
SELECT a.name, b.message FROM a LEFT JOIN b ON a.tid = b.tid
Result 2:
Linux, Very Cool
Linux, What an example
Debian, <null>

The acricle is taken by – http://drupal.org/node/2041 thnx to drupal community.🙂

Tags : mysqljoin , database 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: