MySQL -- JOIN

MySQL –– JOIN
written by: admin

Date Written: 11/9/09 Last Updated: 8/20/10

Creating the Tables

JOIN is important if you need to create complex storage and retrieval of data. For example if you need a program that will create and store a spreadsheet everyday, like a timesheet or performance grid, then you will need to create a VERY complicated and very inefficient table or create a new table everyday that you need to create a spreadsheet (even less efficient and more complicated) or you need to create a few tables that are tied together with JOIN commands. Method 3, also called normalized tables, is by far the best method. It may be a bit tricky to learn at first, but well worth it. There are two other things about normalized tables that will need to be learned like FOREIGN KEYS and Normalized tables. You can read about them later or concurrently to this article.

The Join command will pull the info from 2 or more tables and join them together. It doesn't physically JOIN the tables together.

Here are the two tables we will be looking at

table1

CREATE TABLE test1
(ID INT NOT NULL AUTO_INCREMENT,
col1 TEXT,
col2 DATETIME,
PRIMARY KEY(ID))

table2

CREATE TABLE test2
(ID INT NOT NULL AUTO_INCREMENT,
col3 TEXT,
col4 DATETIME,
PRIMARY KEY(ID))

with data

test1 test2

ID col1 col2 ID col3 col4

1 bird hen 1 bird white
2 canary hen 2 bird silver
3 lion cat 3 cow gold
4 bird blue
5 cow red

Inner JOIN

SELECT * FROM test1 JOIN test2 WHERE test1.col1=test2.col3

is the same as

SELECT * FROM test1,test2 WHERE test1.col1=test2.col3

is the same as

SELECT * FROM test1 JOIN test2 ON test2.col3=test1.col1

is the same as

SELECT * FROM test1 JOIN test2 ON test1.col1=test2.col3

the output for each of these is:

test1 test2

ID col1 col2 ID col3 col4

1 bird hen 1 bird white
1 bird hen 2 bird silver
4 bird blue 1 bird white
4 bird blue 2 bird silver
5 cow red 3 cow gold

Row 1 of table 1 matches rows 1 and 2 of table 2 so the results are displayed until all of the results from table 2 that match the first row of table 1 are displayed.
Row 2 of table 1 matches nothing from table 2 col 2, so nothing is displayed.
Row 3 of table 1 the same is true, so nothing is displayed.
Row 4 of table 1 matches rows 1 and 2 of table 2 so the results are displayed until all of the results from table 2 that match the row 4 of table 1 are displayed.

LEFT OUTER JOIN

The difference between an INNER JOIN and an OUTER JOIN is that NULL values are not ignored.

SELECT * FROM test1 LEFT OUTER JOIN test2 ON test1.col1 = test2.col3

is the same as

SELECT * FROM test1 LEFT JOIN test2 ON test1.col1 = test2.col3

so I just leave the word OUTER out. They will both output:

test1 test2

ID col1 col2 ID col3 col4

1 bird hen 1 bird white
1 bird hen 2 bird silver
2 canary hen
3 lion cat
4 bird blue 1 bird white
4 bird blue 2 bird silver
5 cow red 3 cow gold

This is the same query as example 1 except that where col 1 of rows 2 and 3 of table 1 has no corresponding match in col 1 of table 2 a NULL value is given.

SELECT * FROM test1 LEFT JOIN test2 ON test1.id=test2.id

will output:

test1 test2

ID col1 col2 ID col3 col4

1 bird hen 1 bird white
2 bird hen 2 bird silver
3 lion cat 3 cow gold
4 bird blue
5 cow red

RIGHT OUTER JOIN

select * from test1 RIGHT JOIN test2 ON test1.col1 = test2.col3

test1 test2

ID col1 col2 ID col3 col4

1 bird hen 1 bird white
4 bird blue 1 bird white
1 bird hen 2 bird silver
4 bird blue 2 bird silver
5 cow red 3 cow gold

Every value from row 1 of table 1 is examined to see if it matches row 1 of table 2. If there is no corresponding value in table 1 a NULL value is given.

It should be noted that

SELECT * FROM test1 RIGHT JOIN test2 ON test1.col1 = test2.col3 ORDER BY test1.id

is the same as

SELECT * FROM test1 RIGHT JOIN test2 ON test1.col1 = test2.col3 ORDER BY test1.id

Other JOIN statements include the CROSS JOIN and UNION. CROSS JOIN is the same as LEFT JOIN, but uses different syntax. UNION will list the tables on top of each other as opposed to side by side in the examples above.

Increased Complexity

SELECT people.name, positions.name FROM test2,people,positions where people.id=test2.ID and positions.id=test2.ID

is the same as

SELECT people.name, positions.name FROM test2 JOIN people ON people.id=test2.ID JOIN positions ON positions.id=test2.ID

Explanation:

SELECT people.name, positions.name Gather the rows from column name of table people and from column name of table positions and display them if the conditions from the rest of the query are met.

FROM test2 this is the reference table that will be tested against.

JOIN people ON people.id=test2.ID JOIN positions ON positions.id=test2.ID display the gathered rows where people.ID has the same value as test2.ID and positions.id has the same value as test2.ID.

Links

http://dev.mysql.com/doc/refman/5.0/en/join.html
http://dev.mysql.com/doc/refman/5.1/en/innodb–foreign–key–constraints.html
MySQL –– Normalized Tables
MySQL –– FOREIGN KEYS
MySQL –– UNION
http://articles.techrepublic.com.com/5100–10878_11–6035435.html

TAGS: mysql