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