MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 6.1: INNER JOIN

2009-04-16 21:13:26 | 154 views | inner join mysql sql table primary foreign key

INNER JOIN:



INNER JOIN will let you return all rows that match both tables in a query.


This will be our table 'stores'

|--------------------+--------------------+--------------------|
|store_name |store_sales |store_updates |
|____________________+____________________+____________________|
|Hanks Hardware 2500 2009-01-01 00:00:00 |
|Santos SuperStore 750 2008-12-01 00:00:00 |
|Bobs Boutique 1250 2009-01-01 00:00:00 |
|Pattyïs Pantstore 1050 2008-12-15 00:00:00 |
|Hanks Hardware 2250 2008-01-01 00:00:00 |
+--------------------------------------------------------------+


This will be our table 'producers'

|--------------------+--------------------|
|prod_name |prod_owner |
|____________________+____________________|
|Hammers Hanks Hardware |
|Jeans Pattyïs Pantstore |
|Nails Hanks Hardware |
|Tiberium Toilet NOD Corp |
+------------------------------------------+



We want to join the two tables and show the stores that has a producer and list it.

Syntax:

SELECT table1.column, table2.colum
FROM table1
INNER JOIN table2
ON condition


Example:

The query will look like this:


SELECT stores.store_name, stores.store_sales, producers.prod_name, producers.prod_owner
FROM stores
INNER JOIN producers
ON stores.store_name=producers.prod_owner


|--------------------+--------------------+--------------------+--------------------|
|store_name |store_sales |prod_name |prod_owner |
|____________________+____________________+____________________+____________________|
|Hanks Hardware 2500 Hammers Hanks Hardware |
|Hanks Hardware 2500 Nails Hanks Hardware |
|Pattyïs Pantstore 1050 Jeans Pattyïs Pantstore |
|Hanks Hardware 2250 Hammers Hanks Hardware |
|Hanks Hardware 2250 Nails Hanks Hardware |
+----------------------------------------------------------------------------------+


Here's an excelent moment to show the use of the GROUP BY method. We'll add GROUP BY producers.prod_name to our query, like this:



SELECT stores.store_name, stores.store_sales, producers.prod_name, producers.prod_owner
FROM stores
INNER JOIN producers
ON stores.store_name=producers.prod_owner
GROUP BY producers.prod_name


And here's the shorter, but equaly informative, table output:

|--------------------+--------------------+--------------------+--------------------|
|store_name |store_sales |prod_name |prod_owner |
|____________________+____________________+____________________+____________________|
|Hanks Hardware 2500 Hammers Hanks Hardware |
|Pattyïs Pantstore 1050 Jeans Pattyïs Pantstore |
|Hanks Hardware 2500 Nails Hanks Hardware |
+----------------------------------------------------------------------------------+



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.