DUAL is a special one-row, one-column table in Oracle data dictionary.
DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table — if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:
{ 0 comments }
The “ROWNUM greater than” query never fails to have an eye-popping effect the first time anyone sees it. If you haven’t worked with ROWNUM in Oracle much before, be prepared!
ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:
{ 3 comments }
A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked. Now we want to find and delete those duplicates.
In this article, we’ll see how to achieve this.
{ 5 comments }
The Oracle database does not have the BOOLEAN datatype. No direct way to represent that a column like IS_ACTIVE on table ITEMS is TRUE or FALSE.
The good news is that there are simple workarounds to the situation.
{ 1 comment }
Oracle has three datatypes to store strings. – CHAR, VARCHAR and VARCHAR2. How do they differ from each other? When should you use which?
Let’s find out the answers in this article.
{ 2 comments }
A subquery in the SELECT clause of the main query is called a scalar subquery.
This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is:
[click to continue…]
{ 0 comments }
In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable.
A big mistake.
See how NOT IN and NOT EXISTS behave differently in this small example.
{ 2 comments }
A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS?
The short answer, post-Oracle 9i is:
Both are pretty much the same!
{ 1 comment }
An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery were a table name.
A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:
{ 4 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
