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
      Using Joins

      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.

      Using Joins

      A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause.
      For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc.
      We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables.

      Types of Joins

      There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows −
      • Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join.
      • Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.

      Example

      In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows −
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(15) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS VARCHAR(25),
         SALARY DECIMAL(10, 2),
         PRIMARY KEY(ID)
      );
      
      Let us then insert the following records in the CUSTOMERS table −
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', '32', 'Ahmedabad', 2000),
      (2, 'Khilan', '25', 'Delhi', 1500),
      (3, 'Kaushik', '23', 'Kota', 2500),
      (4, 'Chaitali', '26', 'Mumbai', 6500),
      (5, 'Hardik','27', 'Bhopal', 8500),
      (6, 'Komal', '22', 'Hyderabad', 9000),
      (7, 'Muffy', '24', 'Indore', 5500);
      
      The table is 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
      ORDERS Table −
      We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query −
      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 the ORDERS 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
      Joining Tables −
      Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS.
      SELECT a.ID, a.NAME, b.DATE, b.AMOUNT
      FROM CUSTOMERS a, ORDERS b
      WHERE a.ID = b.CUSTOMER_ID;
      

      Output

      The table is displayed as follows −
      ID
      NAME
      DATE
      AMOUNT
      3
      Kaushik
      2009-10-08 00:00:00
      3000.00
      3
      Kaushik
      2009-10-08 00:00:00
      1500.00
      2
      Khilan
      2009-11-20 00:00:00
      1560.00
      4
      Chaitali
      2008-05-20 00:00:00
      2060.00

      Joins Using a Client Program

      In addition to Join two or more than two tables using the MySQL query we can also perform the Join operation using a client program.

      Syntax

      PHPNodeJSJavaPython
      To join two or more than two MySQL tables through a PHP program, we need to perform the JOIN operation using the mysqli function query() as follows −
      $sql = 'SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID';
      $mysqli->query($sql);
      

      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 a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b 
      ON a.tutorial_author = b.tutorial_author';
      $result = $mysqli->query($sql);
      if ($result->num_rows > 0) {
          while ($row = $result->fetch_assoc()) {
              printf(
                  "Id: %s, Author: %s, Count: %d ",
                  $row["tutorial_id"],
                  $row["tutorial_author"],
                  $row["tutorial_count"]
              );
          }
      } else {
          printf('No record found.');
      }
      mysqli_free_result($result);
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Id: 3, Author: Sanjay, Count: 1