The common perception is that SUBQUERY can’t be used inside the VALUES clause. And frankly speaking, there are many situations on the exam to verify that knowledge. Many people including myself have taken a rule – SUBQUERY can’t be used within VALUES clause. Take for instance an example below. This is a classical subquery inside VALUES clause.

As you can see, Oracle won’t allow you to insert rows using this way.

But the query shown below looks very similar to the first query, it also contains the SUBQUERY in the VALUES clause. From the first glance, it won’t execute. But it does.

The reason it was inserted the following: Yes we have a subquery inside the VALUES clause, but this subquery is not like the subquery in the first case. It is just a scalar subquery used as a first value in VALUES clause. The second value was ‘Great Britain’. If you look at this statement more detail, you will see two pairs of parentheses, not one like in the first query.  So, indeed, it wasn’t a subquery at all. It looked very similar but it wasn’t.

So the rule stays the rule: SUBQUERY can’t be used within VALUES clause. Pay attention to tricks like that, because it is more about paying attention to detail rather than knowledge itself. And the test has many questions like that.