MySQL: Alternate to INNER JOIN

I came across a good way to write queries in mysql. Instead of using INNER JOIN there is a easy way to do it, sort of renaming the tables while writing query. Below is the example:Requires: MySQL

Using:

table1(
     id int,
     name varchar(30),
     age int)
table2(
     id int,
     address varchar(300))
SELECT a.name, a.age, b.address FROM table1 a, table2 b WHERE a.id = b.id;

Explanation:

‘id’ in both the table is primary key.

Look at the query renaming of table is done before retrieving the information we want. WHERE clause is very important in the query because there should be something which is common in both the tables in order to fetch the data. here we have taken id, it should not be necessarily primary key but just have common type.

Note:

  • You can also write queries instead of table name in FROM clause i.e., sub-queries.
  •  If you are not getting results as you expect, there is something wrong with the way query has been written. Try writing it some other way. And this will happen a lot with complex queries. 😛
  • So, this method will make your queries really powerful and easy. Time to replace INNER JOIN.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s