What kind of a query is this? How does it work?

Hi, I recently tried to get employee names and their dept names without a join condition, this query, although succeeds, I can't figure how it works. please help.
select ename,(select dname from dept where deptno=e.deptno) from emp e;

Questions by bhushanyavagal

Showing Answers 1 - 6 of 6 Answers

Blaster_Ayub_Khan

  • Apr 10th, 2012
 

SQL coins the term query as the name for its commands. Basically, all SQL code is written in the form of a query statement and then executed against a database. All SQL queries perform some type of data operation such as selecting data, inserting/updating data, or creating data objects such as SQL databases and SQL tables. Each query statement begins with a clause such as SELECT,UPDATE, CREATE or DELETE.
SQL Query Examples:-----

Code
  1. -- Inserts data into a SQL Database/Table

  2. INSERT INTO orders (customer,day_of_order,product, quantity)

  3. VALUES(Tizag,8/1/08,Pen,4);

  4.  

  5. -- Selects data from a SQL Database/Table

  6. SELECT * FROM orders;

  7.  

  8. -- Updates data in a Database/Table

  9. UPDATE orders SET quantity = 6

  10. WHERE id = 1

  Was this answer useful?  Yes

Youre using a Sub-query -- [(select dname from dept where deptno = e.deptno)] -- to get around using a join clause like -- [FROM EMP E INNER JOIN DEPT D ON (D.DEPTNO = E.DEPTNO)] -- however, you are not actually getting away with not joining the two tables, youre just defining the join in a more complex method. For future coding of your queries it would be better to follow the guide line of Occams razor: "All things being equal, a simpler explanation is better than a more complex one."
To that end, a query that uses a JOIN clause to relate tables is easier to understand, than a query that tries to get around using a JOIN clause.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions