Techie Zone

SQL Joins are basically used to fetch records from multiple tables based on certain conditions. There are various ways of getting records from multiple tables using MySQL Joins. In this article we will learn various techniques of reading records from multiple tables.

Full Join:
MySQL Full Join is used to retrieve combined records from multiple tables used. Say for example table1 has 20 records and table2 has 30 records and if we perform MySQL Full Join it will retrieve 600 records.

Example of Full Join
To illustrate the MySQL JOINS we will be referring to two tables article and article_sold.

create table articles(id int primary key auto_increment, name varchar(100));
 
create table articles_sold(id int primary key auto_increment, invoice_id int, date timestamp default current_timestamp, foreign key (invoice_id) references article(id));

Backup and Restore MySQL Database

We will now insert some entries inside this tables.

	insert into articles(name) values("Nokia 6233");
	insert into articles(name) values("Sandisk 4GB MicroSD Cards");
	insert into articles(name) values("Microsoft Office 2007");
 
	insert into articles_sold(invoice_id) values(2);
	insert into articles_sold(invoice_id) values(3);
	insert into articles_sold(invoice_id) values(3);
	insert into articles_sold(invoice_id) values(2);

So now the table structure will look like.
articles:

Stored Procedures in MySQL

articles_sold:

Regular Expression in MySQL

Now we will perform MySQL Full Join to the above table.

SELECT * FROM articles, articles_sold

This query will return following result:

So if you see it retrieved 18 rows so it is nothing but n x m column.

Prepared Statement in MySQL

Now if we want to filter the record based on certain criteria we will be using “WHERE” keyword in our SQL query.

select * from articles, articles_sold where articles.name='Microsoft Office 2007';

This would return following result:

LEFT JOIN:
MySQL Left Join is used to find rows in one table that have no match in other table. In other words using MySQL Left Join we would be searching for records in second table that does not includes records from first.

Example of LEFT JOIN
You want to search for articles that has not been been sold and also details of articles that are sold.

select a.id as ArticleId, a.name as ArticleName, s.Id as ArticleSoldId, s.article_id as Article_Sold, s.date as articleSoldDate from articles a LEFT JOIN articles_sold s ON a.id = s.article_id;

The above query will return following records.

So if you see the articles_sold did not have entries for Nokia 6233 but the result shows Nokia 6233 also but with all NULL values for articles_sold columns.

On similar lines if we want to search for articles that has not been been sold.

select a.id as ArticleId, a.name as ArticleName, s.Id as ArticleSoldId, s.article_id as Article_Sold, s.date as ArticleSoldDate from articles a LEFT JOIN articles_sold s ON a.id = s.article_id where s.article_id IS NULL;

This will return following result:

RIGHT JOIN
MySQL Right Join is similar to Left Join but it reverses the roles of left and right table. In other words using MySQL Right Join we would be searching for records in first table that does not includes records from second table.

Now if i run the above query using RIGHT JOIN you will see different set of result.
Example RIGHT JOIN:

select a.id as ArticleId, a.name as ArticleName, s.Id as ArticleSoldId, s.article_id as Article_Sold, s.date as ArticleSoldDate from articles a RIGHT JOIN articles_sold s ON a.id = s.article_id;

Result for above query executed:

If you see here that it did not show the records for Nokia 6233, it was because that the articles_sold did not have the entry for Nokia 6233 so it did not show the entries for that.

Related Articles:

• MySql – Working With Stored Procedure
• Export MySQL Database using phpMyAdmin
• MySql Prepared Statement in PHP
• BackUp and Restore Mysql Database
• MySql – Create Foreign Key
• Installing phpMyAdmin
• MySql – Cross Join Queries
• Column count doesn’t match value count in MySql
• MySql Batch Insert Using PHP
• Installing mytop on Linux