Oracle database tutorials, SQL and PLSQL guide, FAQs and interview tips for beginners
Owner: oratabler
Listed in: Technology
Language: English
Tags: oracle, sql, databases, plsql, interviews
Site Statistics
Unique Visitors Today:
0
Page Views Today:
0
Unique Visitors this Week:
0
Page Views this Week:
0
Unique Visitors this Month:
0
Page Views this Month:
0
Total Unique Visitors:
5
Total Page Views:
33
Total Hits Out:
81
Latest Blog Posts for Oratable
- Scalar SubqueryA scalar subquery occurs in the SELECT clause of the main query. This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is like select col1 , ... , (scalar subquery 1) vcol1 , (scalar...
- IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!In our last article we said that in 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 exampl...
- Which is faster – IN or EXISTS?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! Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner qu...
- Inline ViewsAn 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 was a table name. A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row...
- Correlated SubqueryA correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause. For example, a query to list employees whose salary is more than their department’s average: SQL> select oe.deptno, oe.ename, oe.
- Understanding Subqueries and Their TypesA subquery is – to put it simply – a query within a query. What purpose does a subquery serve? A subquery may be needed when it takes more than a single step to reach the answer. Suppose we need to find all employees who work in the same...
- Select Rows With Max ValueWe often need to select rows based on the maximum value of a specific column, such as date. Let’s say we have a table that stores customers orders, and we want to list the last order dates and amounts for each customer. Here is a demo dat...
- Why the SQL WITH clause is not exactly like a function definitionThe 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 u...
- Write Smarter Queries with the SQL WITH ClauseThe WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example. Say we have three departments at our workplace –...
- CASE_NOT_FOUND ExceptionCASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled. Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic –...
Loading Comments...
Comments
{ds_PageTotalItemCount} commentcomments
{pvComments::date}
{pvComments::comment}