Latest Blog Posts for Oratable

• Scalar Subquery
  on Aug 30, 2010 in sql subqueries scalar subqueries
  A 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!
  on Aug 23, 2010 in faq keywords sql exists in
  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?
  on Aug 19, 2010 in faq keywords performance sql subqueries exists in
  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 Views
  on Aug 18, 2010 in sql subqueries inline views
  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 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 Subquery
  on Aug 17, 2010 in sql subqueries correlated subqueries inline views scalar subqueries
  A 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 Types
  on Aug 15, 2010 in sql subqueries correlated subqueries inline views scalar subqueries
  A 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 Value
  on Aug 5, 2010 in analytics faq sql max
  We 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 definition
  on Jul 28, 2010 in exceptions keywords sql ora errors with clause
  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 u...
• Write Smarter Queries with the SQL WITH Clause
  on Jul 26, 2010 in keywords performance sql group by having with clause
  The 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 Exception
  on Jul 23, 2010 in exceptions plsql case when case_not_found if then else
  CASE 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 –...

{pvComments::user_name}

{pvComments::date}

Delete 

{pvComments::comment}

Post a Comment