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 Join

      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 Join

      MySQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.
      Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.

      Working of UNION

      UNION is a type of operator/clause in MySQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.
      The tables are said to be union compatible if they follow the conditions given below:
      • The tables to be combined must have same number of columns with the same datatype.
      • The number of rows need not be same.
      Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.
      Note: Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.

      Syntax

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

      Example

      Let us first create two table "COURSES_PICKED" and "EXTRA_COURSES_PICKED" with the same number of columns having same data types.
      Create table COURSES_PICKED using the following query −
      CREATE TABLE COURSES_PICKED(
         STUDENT_ID INT NOT NULL, 
         STUDENT_NAME VARCHAR(30) NOT NULL, 
         COURSE_NAME VARCHAR(30) NOT NULL
      );
      
      Insert values into the COURSES_PICKED table with the help of the query given below −
      INSERT INTO COURSES_PICKED VALUES
      (1, 'JOHN', 'ENGLISH'),
      (2, 'ROBERT', 'COMPUTER SCIENCE'),
      (3, 'SASHA', 'COMMUNICATIONS'),
      (4, 'JULIAN', 'MATHEMATICS');
      
      Create table EXTRA_COURSES_PICKED using the following query −
      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', 'PHOTOGRAPHY');
      
      Now, let us combine both these tables using the UNION query as follows −
      SELECT * FROM COURSES_PICKED
      UNION
      SELECT * FROM EXTRA_COURSES_PICKED;
      

      Output

      The resultant table obtained after performing the UNION operation is −
      STUDENT_ID
      STUDENT_NAME
      COURSE_NAME
      1
      John
      English
      1
      John
      Physical Education
      2
      Robert
      Computer Science
      2
      Robert
      Gym
      3
      Sasha
      Communications
      3
      Sasha
      Film
      4
      Julian
      Mathematics
      4
      Julian
      Photography

      Working of JOIN

      The Join operation is used to combine information from multiple related tables into one, based on their common fields.
      In this operation, every row of the first table will be combined with every row of the second table. The resultant table obtained will contain the rows present in both tables. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.
      There are two types of Joins:
      • Inner Join
      • Outer Join
      The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join. Other joins like Cross join, Natural Join, Condition Join etc. are types of Inner Joins.
      Outer join includes both matched and unmatched rows from the first table, in the resultant table. It is divided into subtypes like Left Join, Right Join, and Full Join.
      Even though the join operation can merge multiple tables, the simplest way of joining two tables is without using any Clauses other than the ON clause.

      Syntax

      Following is the basic syntax of Join operation −
      SELECT column_name(s)
      FROM table1
      JOIN table2
      ON table1.common_field = table2.common_field;
      

      Example

      In the following example, we will try to join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below −
      mysql> SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, 
      COURSES_PICKED FROM COURSES_PICKED c JOIN EXTRA_COURSES_PICKED e
      ON c.STUDENT_ID = e.STUDENT_ID;
      

      Output

      The resultant table will be displayed as follows −
      STUDENT_ID
      STUDENT_NAME
      COURSE_NAME
      COURSE_PICKED
      1
      John
      ENGLISH
      Physical Education
      2
      Robert
      COMPUTER SCIENCE
      Gym
      3
      Sasha
      COMMUNICATIONS
      Film
      4
      Julian
      MATHEMATICS
      Photography

      UNION vs JOIN

      As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.
      Let us summarize all the difference between these queries below −
      UNION
      JOIN
      UNION operation is only performed on tables that are union compatible, i.e., the tables must contain same number of columns with same data type.
      JOIN operation can be performed on tables that has at least one common field between them. The tables need not be union compatible.
      The data combined will be added as new rows of the resultant table.
      The data combined will be adjoined into the resultant table as new columns.
      This works as the conjunction operation.
      This works as an intersection operation.
      UNION removes all the duplicate values from the resultant tables.
      JOIN retains all the values from both tables even if they're redundant.
      UNION does not need any additional clause to combine two tables.
      JOIN needs an additional clause ON to combine two tables based on a common field.
      It is mostly used in scenarios like, merging the old employees list in an organization with the new employees list.
      This is used in scenarios where merging related tables is necessary. For example, combining tables containing customers list and the orders they made.