faq

Thumbnail image for INNER JOIN and OUTER JOIN Explained

INNER JOIN and OUTER JOIN Explained

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge. Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Thumbnail image for What are CBO and RBO?

What are CBO and RBO?

In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well – get the results in fastest possible time, using the least possible resources. This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options […]

Read the Full Article →
15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

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 […]

Read the Full Article →
Thumbnail image for The Difference Between Views and Materialized Views

The Difference Between Views and Materialized Views

Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion to beginners. Here’s a summary of the key differences between views and materialized views, to […]

Read the Full Article →
Thumbnail image for The Difference Between UNION and UNION ALL

The Difference Between UNION and UNION ALL

Both UNION and UNION ALL concatenate the result sets of two different SQLs. They differ in the way they handle duplicates. UNION performs a DISTINCT on the result set, eliminating any duplicate rows. UNION ALL does not remove duplicates, and is therefore faster than UNION.

Read the Full Article →
Thumbnail image for How to Find Duplicate Records in a Table

How to Find Duplicate Records in a Table

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.

Read the Full Article →
Thumbnail image for What is CTAS?

What is CTAS?

You have probably heard other DBAs/developers and websites talk about creating a table through "CTAS". Does that leave you befuddled? There’s a very simple explanation to that acronym – read on!

Read the Full Article →
Thumbnail image for The Difference between CHAR, VARCHAR and VARCHAR2

The Difference between CHAR, VARCHAR and VARCHAR2

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.

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

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 example.

Read the Full Article →