SQl Join Types

Question

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.employeeNametbl_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.

Sunny Sharma · Answer

Hi Bineesh 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 VP · Answer

Thank you all for giving me valuable information. 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 Pandey · Answer

hiii 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 Rashid · Answer

Hello Bineesh.. 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