Sir, please take look at the following SQL JOINS statements:-
1) select * from tbl_Employee joins tbl_department
on tbl_Employee.employeeId=tbl_department.employeeId
2) select tbl_Employee.employeeName,tbl_department.departmentName
joins tbl_Employee.employeeId=tbl_department.departmentId
3)select employeeName, departmentName from tbl_Employee
where departmentName=(select departmentName from tbl_department where departmentId=1)
I am confused of the above SQL joins, confusion of which kind of join statement to use in a joining of tables.
in which of the above code has more perfection than others when using in a complex situation?
which code has most standard?
which of these query is to be accepted in table joining situation. Let me Know.
Loading
Sunny SharmaPosted Jun 18, 2013, 10:30 AM
The queries that you've mentioned above are all fine and will perform good as all the three queries satisfy different requirement/purpose.
In your first query you're selecting all the columns using join, in your second-you're selective to just EmployeeName and department using join whereas in third query, there's no join but a sub-query. So my point was just to make you sure about all the three have different purpose, so they're not supposed to be compared since all of them serve a different purpose.
But Yes, there are few finding I've for you to optimize your query.
For your first query:
--------------------------------------------
1) select * from tbl_Employee joins tbl_department
on tbl_Employee.employeeId=tbl_department.employeeId
--------------------------------------------
IMO you should avoid selecting all the columns. Instead, mention each of the column that you've to select. This will mitigate the chances to do any changes in your code if in future you add any new column or delete any unnecessary column in your table.
Your second query looks goods and OK to go with.
Coming to the third query:
--------------------------------------------
3)select employeeName, departmentName from tbl_Employee
where departmentName=(select departmentName from tbl_department where departmentId=1)
--------------------------------------------
I suggest you use IN keyword while passing the values from sub-queries to WHERE operator, like:
---------------------------------------------------
select employeeName, departmentName from tbl_Employee
where departmentName IN (select departmentName from tbl_department where departmentId=1)
---------------------------------------------------
This will mitigate any chance of error if your sub-query returns more than one value.
Hope it helps and you got the point.
Cheers!
Bineesh ViswanathPosted Jun 19, 2013, 3:31 AM
Now I also want a one more unsolved problem to be solved by you.
I attached a MS word document with this.
Kindly send me the answers for the questions i wrote in that.
Pankaj PandeyPosted Jun 19, 2013, 12:56 AM
when two tables have mostly different column then use first query, because first query will show all column from table1 +table2.
when you need selected columns use second query, here you define which columns needed .
and 3rd query is a example of subquery.
when you want to get a perticular rows or data then we use subqueries.
thanx
Ibrahim RashidPosted Jun 18, 2013, 9:32 AM
Your 1st and 2nd quiries are inner joined between two table(Use Join Instead of Joins in Query).
And last one is including sub query within a sql query.
Inner Join are Standard but your 2nd query is more efficient than any other(If it is written correctly).
Regards,
Md Ibrahim Rashid