Joins in MySql
It is quite normal to maintain multiple tables within a database, each holding a different type of information. For example, consider the case of a customers table that needs to be able to be cross-referenced with publications purchased from the classics table. Enter the following commands.
 |
SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key |
 |
SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key |
 |
SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key WHERE B.Key IS NULL |
 |
SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key WHERE A.Key IS NULL |
 |
SELECT <select_list> FROM Table A FULL OUTER JOIN Table B ON A.Key = B.Key |
 |
SELECT <select_list> FROM Table A FULL OUTER JOIN Table B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL |
 |
SELECT <select_list> FROM Table A INNER JOIN Table B ON A.Key = B.Key |
Example
CREATE TABLE customers (
name VARCHAR(128),
isbn VARCHAR(128),
PRIMARY KEY (isbn)) ENGINE MyISAM;
INSERT INTO customers(name,isbn)
VALUES('Joe Bloggs','9780099533474');
INSERT INTO customers(name,isbn)
VALUES('Mary Smith','9780582506206');
INSERT INTO customers(name,isbn)
VALUES('Jack Wilson','9780517123201');
SELECT * FROM customers;
Of course, in a proper table containing customers’ details there would also be addresses, phone numbers, email addresses, and so on, but they aren’t necessary for this explanation. While creating the new table, you should have noticed that it has something in common with the classics table: a column called isbn. Because it has the same meaning in both tables (an ISBN refers to a book, and always the same book), we can use this column to tie the two tables together into a single query, as in following example.
SELECT name,author,title from customers,classics WHERE customers.isbn=classics.isbn;
NATURAL JOIN
Using NATURAL JOIN, you can save yourself some typing and make queries a little clearer. This kind of join takes two tables and automatically joins columns that have the same name.
SELECT name,author,title FROM customers NATURAL JOIN classics;
JOIN…ON
If you wish to specify the column on which to join two tables, use the JOIN…ON construct, as follows :
SELECT name,author,title FROM customers JOIN classics ON customers.isbn=classics.isbn;
Using AS
You can also save yourself some typing and improve query readability by creating aliases using the AS keyword. Follow a table name with AS and the alias to use.
SELECT name,author,title from customers AS cust, classics AS class WHERE cust.isbn=class.isbn;
