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      |

+----------------------------------------------------------------------------------+

There are no comments on this article.

Leave a message!

If you have any question or just want to leave a message, just fill out the form below!

Your e-mail will not be visible in your post, it is for validation reasons only

Name:
E-mail:
Comment: