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.

join1 SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key
join1 SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key
join1 SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key WHERE B.Key IS NULL
join1 SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key WHERE A.Key IS NULL
join1 SELECT <select_list> FROM Table A FULL OUTER JOIN Table B ON A.Key = B.Key
join1 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
join1 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;