Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      UNION vs UNION ALL

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      UNION vs UNION ALL

      UNION and UNION ALL operators in MySQL are used to retrieve the rows from multiple tables and return them as one single table.
      However, for these operators to work on these tables, they need to follow the conditions given below
      • The tables to be combined must have the same number of columns with the same datatype.
      • The number of rows need not be the same.

      MySQL UNION Operator

      UNION operator in MySQL works similar to the union operator in relational algebra. This operator combines information from multiple tables that are union compatible. However, only distinct rows from the tables are added to the result table, as UNION automatically eliminates all the duplicate records.

      Syntax

      Following is the syntax of UNION operator in MySQL 
      SELECT * FROM table1
      UNION
      SELECT * FROM table2;

      MySQL UNION ALL Operator

      UNION ALL is also an operator/clause in MySQL, used to combine multiple tables into one. However, this operator also preserves the duplicate rows in the resultant tables.

      Syntax

      Following is the syntax of UNION ALL operator in MySQL 
      SELECT * FROM table1
      UNION ALL
      SELECT * FROM table2;

      MySQL UNION VS MySQL UNION ALL

      The only difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in both tables. Let's try to understand this with an example.

      Example

      First of all we need to create two tables namely with the same number of columns having the same data types. Following query creates a table named "COURSES_PICKED" 
      CREATE TABLE COURSES_PICKED(
         STUDENT_ID INT NOT NULL, 
         STUDENT_NAME VARCHAR(30) NOT NULL, 
         COURSE_NAME VARCHAR(30) NOT NULL
      );
      Following query inserts values into the COURSES_PICKED table 
      INSERT INTO COURSES_PICKED VALUES
      (1, 'JOHN', 'ENGLISH'),
      (2, 'ROBERT', 'COMPUTER SCIENCE'),
      (3, 'SASHA', 'COMMUNICATIONS'),
      (4, 'JULIAN', 'MATHEMATICS');
      The contents of the COURSES_PICKED table will be as follows
      STUDENT_ID
      STUDENT_NAME
      COURSE_NAME
      1
      JOHN
      ENGLISH
      2
      ROBERT
      COMPUTER SCIENCE
      3
      SASHA
      COMMUNICATIONS
      4
      JULIAN
      MATHEMATICS
      Now, lets create another table named EXTRA_COURSES_PICKED as shown below 
      CREATE TABLE EXTRA_COURSES_PICKED(
         STUDENT_ID INT NOT NULL, 
         STUDENT_NAME VARCHAR(30) NOT NULL, 
         EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
      );
      Following is the query to insert values into the EXTRA_COURSES_PICKED table 
      INSERT INTO EXTRA_COURSES_PICKED VALUES
      (1, 'JOHN', 'PHYSICAL EDUCATION'),
      (2, 'ROBERT', 'GYM'),
      (3, 'SASHA', 'FILM'),
      (4, 'JULIAN', 'MATHEMATICS');
      The contents of the EXTRA_COURSES_PICKED table will be as follows
      STUDENT_ID
      STUDENT_NAME
      COURSES_PICKED
      1
      JOHN
      PHYSICAL EDUCATION
      2
      ROBERT
      GYM
      3
      SASHA
      COMMUNICATIONS
      4
      JULIAN
      MATHEMATICS

      Combining tables using UNION Operator

      Now, let us combine both these tables using the UNION query as follows 
      SELECT * FROM COURSES_PICKED
      UNION
      SELECT * FROM EXTRA_COURSES_PICKED;

      Output

      Following is the output obtained
      STUDENT_ID
      STUDENT_NAME
      COURSE_NAME
      1
      JOHN
      ENGLISH
      2
      ROBERT
      COMPUTER SCIENCE
      3
      SASHA
      COMMUNICATIONS
      4
      JULIAN
      MATHEMATICS
      1
      JOHN
      PHYSICAL EDUCATION
      2
      ROBERT
      GYM
      In here, the extra courses and courses picked by "Julian" and "Shasha" are same therefore, the last two records in the COURSES_PICKED and EXTRA_COURSES_PICKED are identical. When we combine both the tables using the UNION operator it excludes the identical records and returns only distinct records.

      Combining tables using UNION ALL Operator

      Unlike the UNION operator if we combine the above created tables using the UNION ALL operator it reruns all the records in both tables 
      SELECT * FROM COURSES_PICKED
      UNION ALL
      SELECT * FROM EXTRA_COURSES_PICKED;

      Output

      The resultant table is displayed as follows
      STUDENT_ID
      STUDENT_NAME
      COURSE_NAME
      1
      JOHN
      ENGLISH
      2
      ROBERT
      COMPUTER SCIENCE
      3
      SASHA
      COMMUNICATIONS
      4
      JULIAN
      MATHEMATICS
      1
      JOHN
      PHYSICAL EDUCATION
      2
      ROBERT
      GYM
      3
      SASHA
      COMMUNICATIONS
      4
      JULIAN
      MATHEMATICS