General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database
UNION is used to combine two or more query result as one query result. All queries must have same no of columns. SQLite is not following data type as strict manner. So columns datatype is not considered here.
However, both query must have same no of column.
Select mobile_name from mobile_sales_details union select mobile_name from mobile_rating Result: 67 records
In the above query we are trying to combine mobile name from both mobile sales and mobile rating table. Both queries have only one column in their select list. Union will not support duplicates. So only it returned only 67 rows because both table have same mobile name only.
Suppose if the first query using two columns, the second query also must have two columns in their select list. UNION ALL does the same work as UNION but it supports duplicates. See the below query
Select mobile_name from mobile_sales_details union all select mobile_name from mobile_rating Result: 134 records
It will display 134 records (67+67). Union and union all are same but the only difference is union will not support the duplicates. Intersect is used to display the common data between two or more queries. As usual am running the same query here, but instead of union am using intersect keyword.
Both the table has same mobile name that is why the below query displaying 67 rows. If only 20 mobile names are common in both table, the result will be only 20 rows.
Select mobile_name from mobile_sales_details intersect select mobile_name from mobile_rating 67 records
Except works like subtraction. First query is considered as source and second query is considered as destination. Except will remove the matched destination data from source and display the unmatched data from the source.
In the below query both table has same mobile name so all mobile names are removed from source. So the result is empty record.
Select mobile_name from mobile_sales_details except select mobile_name from mobile_rating 0 records