Login
Password

Forgot your password?

SQL JOIN STATEMENT IN MYSQL [PART 1]

By Edited Nov 13, 2013 0 0

Introduction

The JOIN statement is a SQL Keyword used to create queries from multiple tables that contain columns from both tables. The simplest form involves two tables that are related via the primary and foreign keys. The diagram shown below helps illustrates the concept of the JOIN statement.

SQL JOIN(101462)

Diagram Key

  • Table 1 represents a master table that has the primary records.
  • Table 2 represents a secondary table that has other information to supplement the primary records.
  • The JOIN operation enables the combining of columns from both tables and present in a single view.

The tables shown below gives a visualization of the above statement.

Table 1

table 1(101463)

Table 2

table 2(101464)

JOIN results 

SQL JOIN Results

Types of JOINS

Inner Join

It combines data from two tables and returns only rows that having matching pairs in both tables. Matching rows are compared on the primary and foreign keys. It has the following syntax.

SELECT [DISTICT | ALL { * | columnList}] FROM TABLE1 JOIN TABLE2 ON TABLE1.ID = TABLE2.F_ID

The above syntax can also be written as

SELECT [DISTICT | ALL { * | columnList}] FROM TABLE1,TABLE2 WHERE TABLE1.ID = TABLE2.F_ID

LEFT OUTER JOIN

The result set produced using the INNER JOIN only returns rows that have matching pairs in both tables. If you want to get even rows that do not have matching pairs in the table on the right, then you use the LEFT OUTER JOIN. It combines data from two tables and returns all the rows from the table on the left even if no matching pairs have been found in the table on the right.

SELECT [DISTICT | ALL { * | columnList}] FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.F_ID


RIGHT OUTER JOIN

 The result set produced using the LEFT JOIN returned all the rows from the table on the left even if no matching pairs were found on the table on the right side [email address]. The RIGHT OUTER JOIN just as the name suggests, does the opposite of what the LEFT JOIN does. It returns all the rows from the table on the right side. It has the following syntax

SELECT [DISTICT | ALL { * | columnList}] FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.F_ID


FULL OUTER JOIN

The result set produced using the FULL OUTER JOIN returned all the row from both tables even if no matching rows have been found.

SELECT [DISTICT | ALL { * | columnList}] FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.F_ID


Summary

  • The JOIN SQL Statement is used to create queries from multiple tables that contain fields from sever al tables.
  • We have about four (4) types of JOIN statements namely
    • INNER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL JOIN
  • MySQL does not support the FULL OUTER JOIN Statement. However, an enumeration can still be produced using the UNION statement.
Look out for Part 2 of this article will show the physical implementation of the article using MySQL and phpMyAdmin.


Advertisement
Advertisement

Comments

Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology