SQL JOIN : Retrieving Data from many Tables

Joins are created when we display Data in queries that using more than one tables. we can connect a row in one table to another row in other table using the same value of both rows. Usualy this relation represented as Primary Key and Foreign Key relationship.

Joins allow an association between a field in one table or view with a field of the same data type in another table or view. Joins show how the data in one table relates to the data in another table. By using joins, data from two or more tables or queries based on logical relationships among the tables can be retrieved. Joins can be added, edited, or deleted to retrieve the data from the database.

For Example you have two tables called Products and Vendors that contain the following Data:

Data in table Vendors:

VendorID  VendorName                  URL
--------- --------------------------- --------------------------
V0001     Microsoft Corporation       http://www.microsoft.com
V0002     Oracle Corporation          http://www.oracle.com
V0003     MySQL AB                    http://www.mysql.com
V0004     PremiumSoft CyberTech Ltd.  http://www.navicat.com
V0005     Mentat Technologies         http://www.mentattech.com

Data in table Products:

ProductID  VendorID  Description
---------- --------- -----------------------------------------
SDB0001    V0001     MS SQL Server Database 2005
SDB0002    V0002     Oracle Database 10g Enterprise
SDB0003    V0003     MySQL 5.0 Enterprise Edition
SDBA0001   V0004     Navicat For MySQL Administration
SDBA0002   V0005     Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   V0005     Dreamcoder 4.1 For MySQL Profesional
SDBA0004   V0005     Dreamcoder 4.1 For MS SQL Server Database

Bellow is SQL JOIN Statement example to display ProductID, VendorName, and Description by Joining two tables above


select products.ProductID,vendors.VendorName,products.Description

from vendors, products

where vendors.vendorid=products.vendorid

Query Result:

ProductID  VendorName                 Description
---------- -------------------------- -----------------------------------------
SDB0001    Microsoft Corporation      MS SQL Server Database 2005
SDB0002    Oracle Corporation         Oracle Database 10g Enterprise
SDB0003    MySQL AB                   MySQL 5.0 Enterprise Edition
SDBA0001   PremiumSoft CyberTech Ltd. Navicat For MySQL Administration
SDBA0002   Mentat Technologies        Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   Mentat Technologies        Dreamcoder 4.1 For MySQL Profesional
SDBA0004   Mentat Technologies        Dreamcoder 4.1 For MS SQL Server Database

Or you can use this form of SQL JOIN, will produce the same result


select products.ProductID,vendors.VendorName,products.Description

from vendors inner join products

on vendors.vendorid=products.vendorid

Query Result:

ProductID  VendorName                  Description
---------- --------------------------- -----------------------------------------
SDB0001    Microsoft Corporation       MS SQL Server Database 2005
SDB0002    Oracle Corporation          Oracle Database 10g Enterprise
SDB0003    MySQL AB                    MySQL 5.0 Enterprise Edition
SDBA0001   PremiumSoft CyberTech Ltd.  Navicat For MySQL Administration
SDBA0002   Mentat Technologies         Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   Mentat Technologies         Dreamcoder 4.1 For MySQL Profesional
SDBA0004   Mentat Technologies         Dreamcoder 4.1 For MS SQL Server Database

MySQL Connector Download

MySQL provide standard database driver connectivity for using MySQL with applications and tools that are compatible with industry standards ODBC and JDBC. This enables MySQL to work easily with standard development tools on Windows, Linux, Macintosh and Unix platforms. Any system that works with ODBC or JDBC can use MySQL.

• Connector/ODBC: Standardized database driver Windows, Linux, Mac OS X, and Unix platforms.


• Connector/J: Standardized database driver for Java platforms and development.


• Connector/Net: Standardized database driver for .NET platforms and development.


• Connector/MXJ: MBean for embedding the MySQL server in Java applications.


• MySQL native driver for PHP - mysqlnd: The MySQL native driver for PHP is an additional, alternative way to connect from PHP 6 to the MySQL Server 4.1 or newer.

MySQL Connector/ODBC 5.1

Version 5.1 of the MyODBC driver is now released by MySQL AB and available for download. An ODBC (Open Database Connectivity) driver enables client programs to access a database or data source. The new version 5.1 MySQL ODBC driver is a greatly enhanced successor version to the existing MyODBC 3.51, rewritten to follow the ODBC 5.1x specifications. It provides complete and up-to-date ODBC functionality when accessing any MySQL database. This latest version is available under the GPL license as well as under commercial license.

MySQL Connector/Net 5.1

Connector/Net 5.1 is the most exciting release of MySQL's fully managed ADO.Net provider to date. Some of the most user-requested features have been included. For example:

• Support for fully commented stored procedures.


• Support for Compact Framework 2.0.


• Support for ASP.Net 2.0 Membership and Role systems.

This release also represents a change in the product packaging. In the past, MySQL AB provided separate downloads for MySQL AB Visual Studio integration product. this product has been merge into a single installer For a better "out of box" experience.

NOTE: By downloading the software from this page, you acknowledge that the software available from here is licensed under the GPL. We advise that you review the GPL before downloading. If you need commercial, non-GPL, licenses, you can order them online.

MySQL Connector/J 5.1

MySQL Connector/J is the official JDBC driver for MySQL. Connector/J 5.1 includes support for JDBC-4.0 functionality, including:

• Ease-of-development features including auto-registration with the DriverManager via the service provider mechanism, standardized Connection validity checks and categorized SQLExceptions based on recoverability/retry-ability and class of the underlying error.


• Unwrapping to interfaces that include vendor extensions


• Support for XML processing via JAXP interfaces to DOM, SAX and StAX


• Support for setting per-connection client information (which can be viewed in the comments section of a query via "SHOW PROCESSLIST" on a MySQL server, or can be extended to support custom persistence of the information via a public interface)


• Support for JDBC-4.0 NCHAR, NVARCHAR and NCLOB types

MySQL native driver for PHP - mysqlnd

The MySQL native driver for PHP is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or newer. It is a replacement for the libmysql, the MySQL Client Library. From now on you can use ext/mysqli either together with libmysql as you did in the past or with mysqlnd.

MySQL AB have no plans to remove libmysql support from ext/mysqli, which would break existing applications. They just add a new, superior alternative to their PHP offerings.

MySQL Visual Studio Plugin 1.02

MySQL Tools for Visual Studio is a package that integrates seamlessly into Visual Studio 2005. This package is currently available under a commercial, no-charge binary license but we are planning to release the source code under an approved open source license within the next few weeks.

The package provides the following features:

• DDEX capability


• Database object manipulation (tables, stored procedures, etc)

Publisher : MySQL AB

Download MySQL Connector/ODBC 5.1 (Windows MSI Installer) : Here (4.1 MB)

Download MySQL Connector/ODBC 5.1 (Linux non RPM packages) : Here (4.3 MB)

Download MySQL Connector/Net 5.1 (sources): Here (3.7 MB)

Download Connector/J 5.1 (ZIP): Here (8.4 MB)

Download MySQL native driver for PHP - mysqlnd Sourcode (tar.gz): Here

Download MySQL Visual Studio Plugin 1.02 (ZIP): Here (650 KB)