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
      Full 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.

      Full Join

      MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.

      MySQL Full Join

      In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results.
      The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword.
      But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred.

      Syntax

      Following is the basic syntax to emulate Full Join −
      SELECT table1.column1, table2.column2...
      FROM table1
      LEFT JOIN table2
      ON table1.common_field = table2.common_field
      
      [UNION | UNION ALL]
      
      SELECT table1.column1, table2.column2...
      FROM table1
      RIGHT JOIN table2
      ON table1.common_field = table2.common_field;
      

      Example

      In this example, we are imitating the full join operation using UNION or UNION ALL keyword. First, let us create a table named CUSTOMERS using the following query −;
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),       
         PRIMARY KEY (ID)
      );
      
      Now insert values into this table using the INSERT statement as follows −
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      Let us create another table ORDERS, containing the details of orders made and the date they are made on.
      CREATE TABLE ORDERS (
         OID INT NOT NULL,
         DATE VARCHAR (20) NOT NULL,
         CUSTOMER_ID INT NOT NULL,
         AMOUNT DECIMAL (18, 2),
      );
      
      Using the INSERT statement, insert values into this table as follows −
      INSERT INTO ORDERS VALUES 
      (102, '2009-10-08 00:00:00', 3, 3000.00),
      (100, '2009-10-08 00:00:00', 3, 1500.00),
      (101, '2009-11-20 00:00:00', 2, 1560.00),
      (103, '2008-05-20 00:00:00', 4, 2060.00);
      
      The table is displayed as follows −
      OID
      DATE
      CUSTOMER_ID
      AMOUNT
      102
      2009-10-08 00:00:00
      3
      3000.00
      100
      2009-10-08 00:00:00
      3
      1500.00
      101
      2009-11-20 00:00:00
      2
      1560.00
      103
      2008-05-20 00:00:00
      4
      2060.00
      Full Join Query −
      On executing the following query, we will produce the union of two tables CUSTOMERS and ORDERS.
      SELECT ID, NAME, AMOUNT, DATE
      FROM CUSTOMERS
      LEFT JOIN ORDERS
      ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
      UNION
      SELECT ID, NAME, AMOUNT, DATE
      FROM CUSTOMERS
      RIGHT JOIN ORDERS
      ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
      

      Output

      The resultant table is produced as follows −
      ID
      NAME
      AMOUNT
      DATE
      1
      Ramesh
      NULL
      NULL
      2
      Khilan
      1560
      2009-11-20 00:00:00
      3
      Kaushik
      3000
      2009-10-08 00:00:00
      3
      Kaushik
      1500
      2009-10-08 00:00:00
      4
      Chaitali
      2060
      2008-05-20 00:00:00
      5
      Hardik
      NULL
      NULL
      6
      Komal
      NULL
      NULL
      7
      Muffy
      NULL
      NULL

      Full Join with WHERE Clause

      With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins.

      Syntax

      The syntax of Full Join when used with WHERE clause is given below −
      SELECT column_name(s)
      FROM table_name1
      FULL JOIN table_name2
      ON table_name1.column_name = table_name2.column_name
      WHERE condition
      

      Example

      Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.
      SELECT ID, NAME, DATE, AMOUNT 
      FROM CUSTOMERS
      LEFT JOIN ORDERS
      ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
      WHERE ORDERS.AMOUNT > 2000.00
      
      UNION 
      
      SELECT ID, NAME, DATE, AMOUNT 
      FROM CUSTOMERS
      RIGHT JOIN ORDERS
      ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
      WHERE ORDERS.AMOUNT > 2000.00;
      

      Output

      The resultant table after applying the where clause with full join contains the rows that has amount values greater than 2000.00 −
      ID
      NAME
      DATE
      AMOUNT
      3
      Kaushik
      2009-10-08 00:00:00
      3000.00
      4
      Chaitali
      2008-05-20 00:00:00
      2060.00