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
      Common Table Expressions

      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.

      Common Table Expression (WITH)

      The MySQL Common Table Expression

      The MySQL Common Table Expression (CTE) is a temporary result-set or table that exists for the duration of a single query. We can use the CTEs to reference the result-set within the context of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, or MERGE statement.
      The scope of a CTE is limited to that specific query. It makes complex queries simple by breaking them into simple blocks.

      The MySQL WITH Clause

      The MySQL WITH clause is used to create CTEs by having one or more comma-separated subclauses. The subclauses provide a subquery that generates the result-set.
      WITH clause cannot be used in MySQL versions before 8.0.

      Syntax

      Following is the syntax of the MySQL WITH clause 
      WITH name_for_summary_data AS (SELECT Statement)
      SELECT columns
      FROM name_for_summary_data
      WHERE conditions <=> (
         SELECT column
         FROM name_for_summary_data
      )
      [ORDER BY columns]

      Example

      Assume we have created a table named DATA containing details such as id, name, age and salary 
      CREATE TABLE DATA(
         ID INT,
         NAME CHAR(20),
         AGE INT,
         SALARY INT
      );
      Now, let us insert values into the table created above using the INSERT INTO statement as shown below 
      INSERT INTO DATA VALUES 
      (101, 'John', 25, 55452),
      (102, 'Jane', 29, 66458),
      (103, 'Arub', 35, 36944);
      In the following example, the WITH clause is used to create a CTE named CTE, which is then queried to retrieve the data from the DATA table 
      WITH CTE AS 
      (Select ID, NAME, AGE, SALARY FROM DATA)
      SELECT * FROM CTE;
      After executing the above code, we get the output as follows
      ID
      NAME
      AGE
      SALARY
      101
      John
      25
      55452
      102
      Jane
      29
      66458
      103
      Arub
      35
      36944

      CTE from Multiple Tables

      We can create CTEs from multiple tables by separating each CTE subclause using a comma (',').

      Example

      Suppose we have created a table with name EMPLOYEE and populated data into it as shown below 
      CREATE TABLE EMPLOYEE(
         ID INT NOT NULL,
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT,
         CONTACT INT
      );
      Here, we are inserting records into the EMPLOYEE table 
      INSERT INTO EMPLOYEE VALUES
      (101, 'Serena', 'Williams', 27, 'F', 9000, 101),
      (102, 'Virat', 'Kohli', 20, 'M', 6000, 102);
      The EMPLOYEE table obtained is as follows
      ID
      FIRST_NAME
      LAST_NAME
      AGE
      SEX
      INCOME
      CONTACT
      101
      Serena
      Williams
      27
      F
      9000
      101
      102
      Virat
      Kohli
      20
      M
      6000
      102
      Now, we create another table CONTACT 
      CREATE TABLE CONTACT(
         ID INT NOT NULL,
         EMAIL CHAR(20) NOT NULL,
         PHONE LONG,
         CITY CHAR(20)
      );
      Let us insert some records in the CONTACT table 
      INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
      (101, 'serena@mymail.com', 'Hyderabad'),
      (102, 'virat@mymail.com', 'Vishakhapatnam');
      The CONTACT table produced is as follows
      ID
      EMAIL
      CITY
      101
      serena@mymail.com
      Hyderabad
      102
      virat@mymail.com
      Vishakhapatnam
      Following example uses Common Table Expressions (CTEs) named "exp1" and "exp2" to separately select specific columns from the EMPLOYEE and CONTACT tables. The final SELECT statement joins these CTEs, combining the chosen columns from each 
      WITH
      exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
      exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
      SELECT * FROM exp1 JOIN exp2;
      Following is the output of the above code
      ID
      FIRST_NAME
      LAST_NAME
      EMAIL
      PHONE
      102
      Virat
      Kohli
      serena@mymail.com
      NULL
      101
      Serena
      Williams
      serena@mymail.com
      NULL
      102
      Virat
      Kohli
      virat@mymail.com
      NULL
      101
      Serena
      Williams
      virat@mymail.com
      NULL