We use cookies to ensure that we give you the best experience on our website.

Decoding SQL grammar — Select SQL Statement with UNION, INTERSECT, and EXCEPT set operators

In order to decode Select SQL Statement with UNION, INTERSECT, and EXCEPT set operators, you need to know how Select SQL Statement with set operators was organized after parsing by GSP library.

Take this SQL for example:

SELECT 1 from dual  -- first part
union 
SELECT 2 from dual  -- second part

There are some properties in TSelectSqlStatement help you to judge whether a Select SQL Statement including set operator or not, if yes, how to get two separate query expressions which is type of TSelectSqlStatement too.

GSP Java version of TSelectSqlStatement

public boolean isCombinedQuery(), this function return true if set operator is used, otherwise, return false. If it’s a Select SQL Statement with set operators, you can’t get select list, from clause and other clause from this statement directly. You should get two separate query expressions by using getLeftStmt() and getRightStmt() which can be a Select SQL Statement with set operators recursively.

public int getSetOperator(), return type of following set operators.

public static final int setOperator_none = 0;
public static final int setOperator_union = 1;
public static final int setOperator_unionall = 2;
public static final int setOperator_intersect = 3;
public static final int setOperator_intersectall = 4;
public static final int setOperator_minus = 5;
public static final int setOperator_minusall = 6;
public static final int setOperator_except = 7;
public static final int setOperator_exceptall = 8;

public TSelectSqlStatement getLeftStmt(), return first part of two separate query expressions.

public TSelectSqlStatement getRightStmt(), return second part of two separate query expressions.

GSP .NET version of TSelectSqlStatement

SelectSetType is type of TSelectSetType. If value is sltNone, then it’s an ordinary Select SQL Statement, otherwise, it’s a Select SQL Statement with set operators.

TSelectSetType = (sltNone,sltUnion,sltUnionAll,sltMinus,sltIntersect,sltIntersectAll,sltExcept,sltExceptAll);

LeftStmt is type of TSelectSqlStatement, return the first part of two separate query expressions.

RightStmt is type of TSelectSqlStatement, return the second part of two separate query expressions.

Once you get a Select SQL Statement with no set operator, you can decode Select SQL Statement illustrated as this document.