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
      Where Clause

      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.

      WHERE Clause

      MySQL WHERE Clause

      We know that the SQL SELECT command is used to fetch records from a MySQL table. In addition to that, we can also use a conditional clause called the WHERE Clause in conjunction with the SELECT statement to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.
      The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

      Operators Used in WHERE Clause

      Here is the list of comparison operators, which can be used with the WHERE clause.
      • =: Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.
      • !=: Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true.
      • >: Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.
      • <: Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.
      • >=: Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.
      • <=: Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.
      Along with these, the WHERE clause can also contain logical operators, like AND, OR and NOT.
      • AND: If an AND operator is used in WHERE Clause with two conditions, the query will return true only if both the conditions are satisfied.
      • OR: If an OR operator is used in WHERE Clause with two conditions, the query will return true only if either of the conditions are satisfied.
      • NOT: If a NOT operator is used in WHERE Clause with a condition, the query will return true only if the table records does not satisfy the condition.

      Fetching Data Using Where Clause

      The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter.
      If the given condition does not match any record in the table, then the query would not return any row.

      Syntax

      Following is the generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table 
      SELECT field1, field2,...fieldN table_name1, table_name2...
      [WHERE condition1 [AND [OR]] condition2.....
      • You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
      • You can specify any condition using the WHERE clause.
      • You can specify more than one condition using the AND or the OR operators.
      • A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

      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)
      );
      The following query inserts 7 records into the above-created table 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );
      Execute the below query to fetch all the records of CUSTOMERS table 
      Select * From CUSTOMERS;
      Following is the CUSTOMERS table
      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 fetch the CUSTOMERS whose AGE is greater than 23 using the MySQL WHERE clause in conjunction with SELECT statement 
      Select * From CUSTOMERS Where AGE > 23;

      Output

      Following are the records
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      7
      Muffy
      24
      Indore
      10000.00

      WHERE Clause Using a Client Program

      Besides using MySQL Where clause to fetch the selected rows from a table, we can also use client programs like PHP, Node.js, Java, and Python to achieve the same result.

      Syntax

      Following are the syntaxes of this operation in various programming languages
      PHPNodeJSJavaPython
      To fetch selective records from a table through PHP program, we need to execute the SELECT statement using the mysqli function query() as 
      $sql = "SELECT COLUMN_NAME1, COLUMN_NAME2,.. FROM TABLE_NAME WHERE CONDITION";
      $mysqli->query($sql,$resultmode)

      Example

      Following are the programs
      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 tutorials_tbl where tutorial_author = 'Sanjay'";
      $result = $mysqli->query($sql);
      if ($result->num_rows > 0) {
         while($row = $result->fetch_assoc()) {
            printf("Id: %s, Title: %s, Author: %s, Date: %d ", 
               $row["tutorial_id"], 
               $row["tutorial_title"], 
               $row["tutorial_author"],
               $row["submission_date"]);               
         }
      } else {
         printf('No record found.');
      }
      mysqli_free_result($result);
      $mysqli->close();

      Output

      The output obtained is as follows 
      Id: 3, Title: JAVA Tutorial, Author: Sanjay, Date: 2007