Technology – What Are The SQL Minus and Except Clauses

Structured Query Language (SQL) is the de-facto query language used in most database management systems (DBMS) such as Oracle and Microsoft SQL Server. This domain-specific language is used in programming to query and return the desired data from a database. We use SQL to write queries that declare what data to expect from a dataset without really indicating how to obtain it. We can also use SQL to update and delete information from a database.

Ideally, in a relational database management system, the database usually runs on the “back end” side of a server in its structured form. By itself, this data is hard to interpret. So, users often have programs on a client computer that help to manipulate that data using rows, columns, fields, and tables. These programs are designed to allow users to send SQL statements to the server. The server then processes these statements by filtering data from the enormous, complex databases and returns results to the user.

Each query begins with finding the data needed then refining it down into something that can be processed and understood easily. To do this, you must use an organized set of operations to get meaningful data from a dataset. This article will explore the Minus Vs except SQL clauses to help you write optimized queries that run fast across various DBMS.

SQL EXCEPT clause

The SQL EXCEPT clause is one of the most commonly used statements that work together with two SELECT statements to return unique rows from a dataset. The SQL EXCEPT combines two SELECT statements to return the row that is present in the first select statement and not in the second.

If you’ve noticed, most SQL clauses do the same thing represented in standard spoken language. For instance, exception literally means not included. SQL EXCEPT is also very similar to the same concept.

The EXCEPT statement returns the distinct row from the left input query that is not output by the right input query. I.e., Returns the resultant rows that appear in query_expression_1 and not in query_expression_2.

SQL EXCEPT Clause Example

Consider a simple situation where you have two tables, one with dog names and the other one with cat names.

Cats Data Set

+———+———–+

| CatId | CatName |

|———+———–|

| 1 | Boss |

| 2 | Scarlet |

| 3 | Fluffy |

| 4 | Fluffy |

+———+———–+

Dogs Data Set

+———+———–+

| DogId | DogName |

|———+———–|

| 1 | Yelp |

| 2 | Woof |

| 3 | Boss |

| 4 | Boss |

+———+———–+

Using the SQL Except statement, we can filter the dataset and return only the distinct rows from the left SELECT query that have not been returned by the SELECT query on the right side of the EXCEPT statement.

An example SQL syntax query would look like this:

SELECT CatName FROM Cats

EXCEPT

SELECT DogName FROM Dogs;

In a typical scenario, a client program will send this query to the “back-end” server. This statement is then processed and only returns the values available in the “cats” dataset that don’t appear in the “dogs” dataset. When two rows are similar, as is the case with “fluffy,” only one row is returned. This is because the SQL query only returns distinct rows.

Here’s the result of the above query:

+———–+

| CatName |

|—————|

| Fluffy |

| Scarlet |

+————– +

Common SQL Except Rules
  • You must have the same number of columns in both queries
  • The column order must be the same in all queries
  • The column data types must be compatible with each other. The data types really don’t have to be the same, but they MUST be comparable through implicit conversion.
  • The EXCEPT statement returns all records from the 1st SELECT statement not available in the second SELECT statement
  • The EXCEPT operator in the SQL server is similar to the MINUS operator in Oracle.
  • MySQL does not support SQL Except clause. The workaround is to use the LEFT JOIN clause when using MySQL.

SQL MINUS Clause

MINUS operator does the same thing as the EXCEPT clause. But unlike the EXCEPT clause, the MINUS operator is only supported by Limit number of databases:

Database NameMinusExcept
Amazon RedshiftNoYes
VQL (denodo)YesNo
ElasticsearchYesNo
MariaDBYesYes
IBM Db2NoYes
Microsoft SQL ServerNoYes
MongoDBNoNo
MySQLNoNo
OracleYesNo
PostgreSQL No Yes
SOQL (salesforce)NoNo
snowflakeYesYes
SQLiteNoYes

The MINUS operator compares two queries and only returns the rows present in the first dataset but are not output by the second set. The result usually contains the distinct rows available in the left Select statement that aren’t included in the results of the right select statement.

Here is a typical MINUS syntax:

SELECT column_list_1 FROM T1

MINUS

SELECT column_list_2 FROM T2;

Common Oracle Minus Operator Rules

For MINUS operator to work, the dataset must conform with rules similar to those of SQL EXCEPT clause:

  • The data type of corresponding columns must be similar (Either Numeric or Character)
  • The order and number of columns must be the same.
  • The column used for ordering can be defined by the column number.
  • Duplicates are automatically eliminated in the final result.

Conclusion

The Minus Vs Except SQL Clause comparison can be confusing for many people. These two clauses are synonymous with each other and have similar syntax and results. Both Minus and Except help users skim through datasets to identify unique rows available only in the first SELECT query and not returned by the second SELECT query.

Technology – Denodo VQL To Get A List Of Cached View Names

Hello, this is a quick code snippet of a Denodo VQL (Denodo Virtual Query Language) to pull a list of cached view names which can be useful in pulling list of cached views. It’s not a complicated thing, but now that I’ve bothered to look it up on putting this note here mostly for me but you may find useful. I have found this useful for several reasons not the least of which is for creating jobs to do maintenance of cached view statistics.

Example VQL List Of Cached View Names

select name view_name

from get_views()

       where cache_status <> 0

       and database_name = ‘uncertified’

       and name not like ‘%backup’

       and name not like ‘%copy’

       and name not like ‘%test’

       and name <> ‘dv_indexes’;

Denodo Reference Links

·         Denodo > Denodo Platform 8.0 > User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_VIEWS

Technology – Denodo View Performance Best Practice

Since I have been doing more training of beginning users of the Denodo, there have been a lot of questions around performance best practices and optimization. This article is a quick summary of some of the high points of the Donodo documentation, which are typically useful.

However, I would like to point out that the performance of Denodo views is:

  • usually, an ongoing process as your environment evolves and that your code changes
  • also, the performance of Denodo views may involve elements be on the Denodo framework itself, such as source system databases
  • and may require some administration configuration and reengineering to achieve your full benefits in terms of establishing environment sizing, data movement databases, use of bulk load processes, and maintenance processes (E. G., Scheduled index maintenance, scheduled statistics maintenance)
  • furthermore, good general SQL and coding practices have a great deal to do with performance unrelated to the denotative toolset.

Avoid ‘Create View From Query’

Using ‘Create view from Query’ to create base views bypasses the denodo optimization engine and pushes directly to the data source as written.

Make Sure Primary Keys (PK) And Unique Indexes Have Been Set

Accurately setting the primary key (PK) on views (especially, base views): aides:

  • The static optimization phase, primary keys, and unique indexes enable Join pruning and Aggregation push-down when appropriate
  • The Primary Key is presented to consuming applications and RESTFUL web services
  • Allow browsing across the associations of views with Data Catalog.

Mirror Source System Indexes

Aiding source database indexes to denodo base views aids the denodo optimizer to make appropriate decisions. However, avoid adding indexes on the table which do not exist in the source database. This will cause the optimizer to make incorrect execution plans and will undercut performance.

Note: Primary Keys (PK) are enforced by denodo, only used to enable optimization and application capabilities.

Apply Foreign Key (FK) And Referential Constraint Associations

An association represents a foreign key relation.  However, when a referential constraint is applied to an association, every row of the ‘Dependent’ view has a matching value in the ‘Principal’ view, which meets the Condition mapping. 

Adding Indexes To Cache Views

Adding Primary Keys and Unique indexes to cached tables also aids the optimizer and, if properly maintained, aids normal database operation when querying cache tables.

Gather and Maintain View Statistics

View statistics play an essential role, helping the optimizer make decisions about execution plans and data movement.  Statistics are most important for base views and cached, especially the total rows, average size, and distinct view values.

Caching Derived Views

Caching large, long-running, complex views can improve performance and limit source system impacts and Denodo if the cache guidelines are followed.  However, to optimize efficient cached views should have Primary Keys, Unique Indexes, performance indexes, and Statistics. See caching Guidelines for additional detail.

Use Effective Joins

Effective joins are essential to performant view.  Here are some high-level tips to keep in mind when building joins:

  • When possible, use simple join condition
  • Join on primary keys or unique indexes
  • Leverage Foreign Key (FK) and Primary Keys (PK).  Especially when an association referential constraint is defined
  • Use Inner joins when Possible
  • When using outer joins, Organize joins by data source when using multiple data sources
  • Avoid using view parameters and subqueries on the join condition

Use A Building Block Approach

Breaking views into discreet units allows the optimizer more opportuning to optimize SQL’s and performance.  Here are a few tips for using the building block approach:

  • Create views for different entities (Fact, Dimension, or Subject set)
  • Build views for discreet and/or distinct data subsets
  • Use SQL tuning rules to arrive at the smallest result set as soon as possible
  • Tune each view individually

Let Denodo Determine Optimal Data Movement

Where possible, avoid manually assigning data movement strategy.  Letting Denodo determine the optimal data movement strategy (assume other view optimizations have been applied) provides the greatest flexibility as the data changes across time.  When precursor views are updated and/or tuned and Prevents errors due to data movement strategy conflicts.

Denodo Reference Links

Best Practices to Maximize Performance I: Modeling Big Data and Analytics Use Cases

Best Practices to Maximize Performance II: Configuring the Query Optimizer

Best Practices to Maximize Performance III: Caching

Best Practices to Maximize Performance IV: Detecting Bottlenecks in a Query

Denodo Knowledge Base > Performance & Optimization

Denodo Knowledge Base > Performance

Denodo E-books

Denodo Cookbook: Query Optimization

Denodo – Script To Alter Association Metadata Description

Here is a Denodo Virtual Query Language (VQL) code snippet, which I use to populate the metadata description of Denodo Associations in Virtual DataPort (VDP).  This snippet identifies Denodo Association without a Metadata Description and fills Metadata Description with a simple explanation of what the Denodo Association’s purpose.

VQL To Generate Association Alter Statements Description Update

select ‘ALTER ASSOCIATION’||’ ‘|| association_name || ‘ ‘||’DESCRIPTION =’|| ””|| ‘Performance association between’ ||’ ‘ || left_view_name || ‘ ‘|| ‘and’ ||’ ‘|| right_view_name ||””||’;’

FROM GET_ASSOCIATIONS()

WHERE input_database_name = ‘<<Database_Name>>’

AND input_type = ‘views’

AND len(trim(association_description)) <1

Add a View Name

Add this line to generate the association’s description updates for a specific view:

AND left_view_name = ‘<<View_Name>>

Vendor References

Denodo >User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_ASSOCIATIONS

https://community.denodo.com/docs/html/browse/8.0/en//vdp/vql/stored_procedures/predefined_stored_procedures/get_associations

Denodo – Find Views With Missing Metadata Description

Here is a Denodo code snippet, which I use to identify views that do not have the metadata description populated.  Granted, this is a simple thing, but I have found it useful when validating that all Denodo views have been described in Virtual DataPort (VDP).

SELECT *
FROM GET_VIEWS()
WHERE
input_database_name = ‘Database_Name
And len(description) = 0;

SQL Server Length Function Equivalent

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

Denodo – Obtaining The Difference Between Timestamps Or Microsecond Fields

The topic of how to obtain the difference or duration from timestamps having milliseconds has come up when working in Denodo a few times. So, after having clarified for different developers I thought I’d create this quick reference to clarify the subject little bit.

To get the difference with the duration you must subtract the start date field from the in the field (assuming you’re working with the after 1970), and then, divided by the microseconds the time unit in which you wish to display the result.

It’s basically boils down to:

  • displaying the resultant milliseconds, no division is required
  • displaying the results in seconds, divided by 1,000
  • displaying the results in minutes, divided by 60,000
  • displaying the results in hours, divided by 3,600,000

Example Denodo Code Shells

Here are some quick code shells with explanation above:

Duration in milliseconds

(gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>)) AS duration_millis

Duration seconds

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/1000) AS duration_seconds

Duration in minutes

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/60000) AS duration_minutes

Duration and hours

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/36000000) AS duration_hours

Related References

Denodo > User Manuals > Virtual DataPort VQL Guide > Appendix > Syntax of Condition Functions > Date Processing Functions

Denodo > Home > Q&A > TIMESTAMPDIFF

How To connect DBeaver To Denodo Using JDBC

Here is a quick outline of how to configure DBeaver to connect to denodo using JDBC and a few references with other helpful information.

Copy jdbc driver from denodo to dbeaver

  • Copy from denodo engine server location: <DENODO_HOME>/tools/client-drivers/jdbc/denodo-vdp-jdbcdriver.jar to Dbeaver loaction: C:\Program Files\DBeaver\plugins

Configure DBeaver Driver

  • In DBeaver, navigate to Database > Driver Manager. 
  • Choose the ‘New’ button
  • Populate the Setting properties
    • Name: Denodo 7.0
    • Driver Type: Generic
    • URL Format: jdbc:vdb://<hostname>:<port>/<database>
    • Class Name: com.denodo.vdp.jdbc.Driver
  • In ‘Libraries’, Choose ‘Add File’ and navigate to ‘C:\Program Files\DBeaver\plugins’, Choose the denodo jar file and Click ‘Open’
  • Click ‘OK’ to Create and save the driver configuration.
  • Then, the Driver will be available in the Driver Manager for use

Related References:

Download URL for DBeaver software

DBeaver > Database Drivers

Connecting from your application – JDBC Clients

List of denodo supported JDBC databases

The question ‘What are the JDBC databases supported by denodo” is one of those questions, which always seems to come up either in customer meetings, Proof Of Concept (POC) implementations, or planning for POC’s. While it is documented by denodo, I seem to spend more time looking for it than I should. So, I thought it might be useful to document the Supported JDBC Data Sources page URL for easy reference.

Related References

Denodo > User Manuals > Virtual DataPort Administration Guide > Appendix > Supported JDBC Data Sources

How to save Denodo Virtual Data Port (VDP) VQL Shell results

In a recent project, I was asked by a new user of Denodo Virtual Data Port (VDP) how to save denodo VDP VQL Shell results. So, here is a simple outline of the process of exporting the VQL Shell results:

  • Execute the VQL Shell SQL
  • Click on the ‘Save’ button in the ‘Results’ tab to save the results
  • A dialog box will open
  • Check  ‘include Results’ checkbox
  • Set the separator character
  • Set the path of the Output file and desired filename.
  • If you want to the results with a header in a delimited file, enable ‘Include header’ checkbox.
  • Click ‘OK’