//AMIS Technology Blog » anti join

Posts tagged anti join

image.png

Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring

0
2 months ago
by Lucas Jellema in ,

The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:

The challenges I will discuss in this article are simple:

  • which sequences of three color observations occur in the data set
  • how often do these sequences of three occur (give me the top 3 sequences)
  • given the colors that have been observed, which combinations of three can be created
  • which possible combinations (or color sequences of three colors) have not been observed at all

Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.

(more…)

SIG Event

Anti-Search patterns – SQL to look for what is NOT there – Part One

5
2 years ago
by Lucas Jellema in , , ,

Usually in SQL queries, you will try to retrieve information from a database by reading specific records from the relevant tables. However, there are situations where what you want to learn from a query is the fact that something is NOT there. So instead of querying for existing records, you are looking for records that do not exist. This article discusses a number of such situations and demonstrates several “anti-queries”. Where possible, I have used the EMP and DEPT table of the classical SCOTT sample schema to illustrate the point.

Some of these examples are perhaps better used to illustrate specific SQL features – such as CONNECT_BY_ISLEAF, PARTITION OUTER JOIN, LISTAGG, MINUS, SCALAR SUBQUERY and more – than to answer the questions at hand.

(more…)

Go to Top