Keywords have a special meaning in the Oracle database, and a specific role to play in the context they appear. FUNCTION, MAX, COMMIT are some examples of Oracle keywords.
When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.
The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.
ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.
Oracle’s MERGE statement is tailor-made for situations when you want to do an "upsert" i.e. update existing rows in a table or insert new rows depending on a match condition. This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query). In place of 3 separate unwieldy INSERT, UPDATE and DELETE statements with conditional sub-queries, the all-in-one MERGE does the job in one shot.
A typical query scenario: you want to sort data in descending order, say students arranged by their GMAT scores. Given a table student (id, name, score), what can be simpler than adding an ‘ORDER BY score DESC’ to the query?
If that’s what you thought, here’s a complication. There are some students who did not take the GMAT at all. Their scores in the table are not zero, they are NULL. Oracle’s ORDER BY..DESC in this situation could give you a nasty surprise with the result.
Here’s how the result will look with the ORDER BY…DESC clause:
A query using LTRIM and TO_CHAR seemed to be behaving oddly, but soon a simple explanation presented itself.
Check out the query below. Can you tell why it gives the answer it does?
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:
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.
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!
The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.
BUT there is a difference.
