General Note:

Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

Inner join is our own condition to retrieve data from multiple tables. To write condition we are using equal (=) operator. That is why this in join is called as EQUI Join.

This works same as like natural join. But when we use this type of joins? If we have same column name in both table means, we can use natural join. But if you don’t have same column name in both table we will use equi join. In the below query, in where condition, we have mentioned table name before the column name because we do not have different column names in both tables.

If you have different column you can directly use column name alone without table name.


Select mobile_sales_details.mobile_name,price,brand_name,star5 from mobile_sales_details 
join
mobile_rating on mobile_sales_details.mobile_name=mobile_rating.mobile_name

 

mobile_name             price       brand_name star5    

---------------------- ---------- ---------- ----------

Moto X (16 GB) (Black) 23999       Motorola   20072    

Moto G (Black with 16 13999       Motorola   2078    

Moto E (Black)         6999       Motorola   12900    

Asus Zenfone 4 A400CG   5999       Asus       195      

Asus Zenfone 5 A501CG   9999       Asus       465      

Asus Zenfone 5 A501CG   12999       Asus       114      

Asus Zenfone 6 A600CG   16999       Asus       74      

Alcatel Onetouch Idol   16999       Alcatel     368      

Samsung Galaxy S Duos   8206       Samsung     981      

Samsung Galaxy Star Pr 7599       Samsung     348      

Samsung Galaxy S Duos   8214       Samsung     981      

Samsung Galaxy Core I8 11795       Samsung     516      

Samsung Galaxy Grand 2 18990       Samsung     393      

Samsung Galaxy Trend S 9610       Samsung     128      

Alternative to cross join is the comma operator. The same cross join query is re-written with, comma operator. It will produce the same result as cross join.


Select mobile_sales_details.mobile_name, price, brand_name, star5
from mobile_sales_details,mobile_rating

The same equi join can be written with comma operator and a condition without join keyword.


Select mobile_sales_details.mobile_name, price, brand_name, star5 from
mobile_sales_details, mobile_rating 
where mobile_sales_details.mobile_name=mobile_rating.mobile_name

If you going to join N tables, you must write N-1 condition or else the result will be a cross join. If you are joining three tables, you must write minimum two conditions or else the result will be a cross join.

Take a look at our above queries except cross join. Natural join, Using, Inner join all writes a single condition for joining two tables. That is why we getting proper result.

ALIAS FOR TABLE:

We can give alias name to tables like columns. Why we are going for table alias? While joining tables, the table name may be too long at some cases, at that time we can give alias name to reduce it.

Table alias will not change your result.


Select m1.mobile_name, price, brand_name, star5 from
mobile_sales_details m1, mobile_rating m2 
where m1.mobile_name=m2.mobile_name