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
      MINUS Operator

      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.

      MINUS Operator

      MySQL MINUS Operator

      The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table.
      Let's consider the following diagram to understand the MINUS operation:
      
      As we can see in the above diagram, the MINUS operator returned the table containing 1 and 3 values as output because they are the distinct values in table1 which do not exist in table2.
      MySQL does not support the MINUS operator, we can use the LEFT JOIN instead of the MINUS operator.

      Syntax

      Following is the basic syntax of the MINUS operator 
      SELECT column_lists FROM table_name WHERE (condition)
      MINUS
      SELECT column_lists FROM table_name WHERE (condition);
      Unfortunately MySQL does not support the MINUS operator. However we can use the MySQL JOIN clause instead of MINUS operator.
      Following is the syntax for JOIN clause to perform MINUS operation 
      SELECT column_list FROM table_name1
      LEFT JOIN table_name2 ON join_predecate
      WHERE table_name2.column_name IS NULL;

      Example

      Firstly, 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)
      );
      Here, we are inserting some records into the CUSTOMERS table using the INSERT statement 
      INSERT INTO CUSTOMERS VALUES 
      (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);
      The table will be created as
      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
      Now, let us create another table named ORDERS using the below query 
      CREATE TABLE ORDERS (
         OID INT NOT NULL,
         DATE VARCHAR (20) NOT NULL,
         ID INT NOT NULL,
         AMOUNT DECIMAL (18, 2)
      );
      Here, we are inserting some records into the ORDERS table using the INSERT INTO statement 
      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 created as follows
      OID
      DATE
      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
      As MySQL does not support the MINUS operator instead we can use the JOIN to perform this operation
      The following query selects all the customers who do not have any orders by joining two tables 
      SELECT ID, NAME, AGE FROM CUSTOMERS
      LEFT JOIN ORDERS USING (ID)
      WHERE ORDERS.ID IS NULL;
      

      Output

      The following are the customers who do not have any orders:
      ID
      NAME
      AGE
      1
      Ramesh
      32
      5
      Hardik
      27
      6
      Komal
      22
      7
      Muffy
      24

      Minus Operator Using Client Program

      In addition to executing the Minus Operator in MySQL table using an SQL query, we can also perform the another operation on a table using a client program.

      Syntax

      Following are the syntaxes of the Minus Operator in MySQL table in various programming languages
      PHPNodeJSJavaPython
      To perform the Minus Operator in MySQL through a PHP program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysqli connector.
      $sql = "SELECT column_lists FROM table_name WHERE (condition)
      LEFT JOIN SELECT column_lists FROM table_name WHERE (condition)";  
      $mysqli->query($sql);

      Example

      Following are the implementations of this operation in various programming languages
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if($mysqli->connect_errno ) {
         printf("Connect failed: %s", $mysqli->connect_error);
         exit();
      }
      //printf('Connected successfully.');
      $sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL";
      $result = $mysqli->query($sql);
      if ($result->num_rows > 0) {
         printf("Table records: \n");
         while($row = $result->fetch_assoc()) {
            printf("CUST_ID %d, NAME %s, ADDRESS %s, SALARY %f, OID %d, DATE %s, ADDRESS %s, AMOUNT %f",  
            $row["CUST_ID"],
            $row["NAME"],
            $row["ADDRESS"],
            $row["SALARY"],
            $row["OID"],
            $row["DATE"],
            $row["ADDRESS"],
            $row["AMOUNT"],);
            printf("\n");         
         }
      } else {
         printf('No record found.');
      }
      mysqli_free_result($result);
      $mysqli->close();

      Output

      The output obtained is as follows 
      Table records:
      CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
      CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
      CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
      CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000