Codinko- Java Coding Tutorials

Category: oracle

Oracle select query with inner select query error-ORA-00907: missing right parenthesis


/ this gets executed

SELECT empid FROM employees WHERE deptid IN (10,20,30,40 );

// this gets executed

SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC 

but the below query throws error:

SELECT empid  FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC);



The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall

This article well explains many of the concepts –

“ORA-00907: missing right parenthesis Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don’t use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn’t make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn’t care about the row order inside an IN clause:”

I tried the SQL statement with WHERE clause and ‘=’ instead of ‘IN’ and it still threw the error:’missing right parenthesis‘.

conclusion 1 :

“Don’t use ORDER BY in the WHERE clause subquery” or “Subqueries in the where clause are not allowed to use ORDER BY in Oracle”

Conclusion 2

This case-study also shows the scenario where we should go for JOIN rather than select subquery

SQL Interview Questions

Online try-it tool –!4/9de39/590

1. Find the result of the below query, Given the following tables:

sql> SELECT * FROM authors;
| id | name         |
|  1 | John     |
|  2 | Peter    |
|  3 | Steve  |
|  4 | Shane  |

sql> SELECT * FROM books;
| id | book          | author_id |
|  1 | book1          |  2        |
|  2 | book2          |  3        |
|  3 | book3 .        |  2        |
|  4 | book4          |  NULL     |

What will be the result of the query below?

                      (SELECT author_id FROM books)

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.


Solution: Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many id matches on both tables.

Knowing this, a query that avoids this issue would be as follows:

      (SELECT author_id FROM books WHERE author_id IS NOT null)

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

2.  Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query:

Select * From Emp, Dept

Explain your answer.

Solution: The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.


Given two tables created as follows

create table test_a(id numeric);

create table test_b(id numeric);
insert into test_a(id) values (10);
insert into test_a(id) values (20);
insert into test_a(id) values (30);
insert into test_a(id) values (40);
insert into test_a(id) values (50);
insert into test_b(id) values (10);
insert into test_b(id) values (30);
insert into test_b(id) values (50);

Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.

Solution:  In Oracle, the minus keyword is used instead. Note that if there are multiple columns, say ID and Name, the column should be explicitly stated in Oracle queries: Select ID from test_a minus select ID from test_b


Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

(Note: You may assume that there are at least 10 records in the Employee table.)

Solution :This can be done as follows:

    SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary

This works as follows:

  • First, the SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 10th highest salary.
  • Therefore, the second query reorders the 10 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 10 salaries).

Not all databases support the TOP keyword. For example, MySQL  use the LIMIT keyword, as follows:

) AS Emp ORDER BY Salary LIMIT 1;

Or even more concisely, in MySQL this can be:


Oracle does not support TOP. Use ROWNUM 

SELECT * FROM your_table

and apply the same logic.

Write SQL query to find second highest salary in employee table? 

You can also use keyword’s like TOP or LIMIT if you are using SQL Server or MySQL

The simplest way to find 2nd highest salary is following:

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

This query first finds maximum salary and then exclude that from the list and again finds maximum salary. Obviously second time, it would be second highest salary.

5.  What is an execution plan? When would you use it? How would you view the execution plan?


  • An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query.
  • Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
  • In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well.
  • In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.


To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

  SELECT last_name FROM employees;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. See “Displaying PLAN_TABLE Output”.

6. SQL interview questions – watch in video below-

7. Union vs UnionAll, Count(*) vs count

8. View vs Materialized view

9. Find second highest repeated/duplicated salary

10. How database behaves in multithreading environment?

11. Questions related to Join and scenarios.

12. Aggregate function related questions

13. Analytical function related questions

14. Having vs Where


Copyright © 2020 Codinko- Java Coding Tutorials

Theme by Anders NorenUp ↑