IBM i SQL
Materialized Query Table(MQT) A materialized query table is a table whose structure and definition is based on the result of a given query. Materialized query table get its data from same query. And MQT actually contains that data. That query may contain more than 1 table.
SQL SEQUENCE OBJECT SEQUENCE OBJECT are similar to IDENTITY COLUMN in the way that they both generate unique values and use almost same attributes. However, sequences are objects are independent of any tables. Sequences are not tied to a column in a table and are accessed separately. Additionally, they are not treated as any part of a [...]
Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to IDENTITY COLUMNS. But rather than being an attribute of a numeric column, it is a separate data type. Same as IDENTITY COLUMNS there are 2 options for GENERATED clause. ALWAYS BY DEFAULT
IDENTITY COLUMN An IDENTITY COLUMN is a numeric column defined in a table for which the column values can be generated automatically by DB2. Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC are allowed for identity columns. Maximum one identity column per table is allowed IDENTITY COLUMN is implicitly NOT NULL.
Let say you add a column ID in your table and you want an incremented value for this column every time a new record is inserted in Table or later you want to re-sequence the same ID column starting from 1. There can be multiple tricks to achieve this but SQL provides multiple very simple and handy option to do all these tasks where a [...]
SQL INTERSECT SQL INTERSECT return records which are common in all the SELECT queries involved in operation. INTERSECT only return unique value, all duplicate values are removed from final result. SELECT id1 FROM table1 INTERSECT SELECT id2 FROM table2
SQL EXCEPT SQL EXCEPT returns records from first SELECT query which are not present in any other SELECT queries. EXCEPT only return unique value, all duplicate values are removed from final result. SELECT id1 FROM table1 EXCEPT SELECT id2 FROM table2
SQL UNION UNION SET operator return unique record from all SELECT queries involved in operation.For UNION (or any Other SQL SET OPERATION) to work Number of selected columns from all SELECT queries must match. In this given code each query have one selected columns “id1” and “id2”. Data type of selected columns [...]
SQL JOINS allow you to combine records for multiple tables based on some condition. This condition in most cases is “common value(s)” between tables needed to join. But there are options of more conditions. If you have worked on DB2 JOIN LOGICAL FILE it has almost same concept as SQL JOINS but SQL JOINS give you more options [...]