Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to use Straight Join in MySQL?
The straight join in MySQL works like inner join or join. This means that it returns only the matching rows. Firstly, we need to understand Straight join in MySQL. For that, we need to create two tables and relate both the tables with foreign key constraints.
Here is the first table
mysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), -> FK int -> ); Query OK, 0 rows affected (0.47 sec)
Here is the second table −
mysql> CREATE table PrimaryTableDemo -> ( -> FK int, -> Address varchar(100), -> primary key(FK) -> ); Query OK, 0 rows affected (0.47 sec)
After that, we will add constraints −
mysql> ALTER table ForeignTableDemo add constraint FKConst foreign key(FK) references PrimaryTableDemo(FK); Query OK, 0 rows affected (1.54 sec) Records: 0 Duplicates: 0 Warnings: 0
Now add records into the second table −
mysql> INSERT into PrimaryTableDemo values(1,'US'); Query OK, 1 row affected (0.10 sec) mysql> INSERT into PrimaryTableDemo values(2,'UK'); Query OK, 1 row affected (0.14 sec) mysql> INSERT into PrimaryTableDemo values(3,'Unknown'); Query OK, 1 row affected (0.08 sec)
Displaying records
mysql> SELECT * from PrimaryTableDemo;
The following is the output −
+----+---------+ | FK | Address | +----+---------+ | 1 | US | | 2 | UK | | 3 | Unknown | +----+---------+ 3 rows in set (0.00 sec)
Now, add records into the first table
mysql> INSERT into ForeignTableDemo values (1,'John',1); Query OK, 1 row affected (0.20 sec) mysql> INSERT into ForeignTableDemo values (2,'Bob',2); Query OK, 1 row affected (0.27 sec)
Displaying all the records with the help of SELECT statement −
mysql> SELECT * from ForeignTableDemo;
The following is the output
+------+------+------+ | Id | Name | FK | +------+------+------+ | 1 | John | 1 | | 2 | Bob | 2 | +------+------+------+ 2 rows in set (0.00 sec)
The query for straight join is as follows that displays only matching rows −
mysql>SELECT ForeignTableDemo.Id, ForeignTableDemo.Name, PrimaryTableDemo.Address -> from ForeignTableDemo -> Straight_join PrimaryTableDemo -> on ForeignTableDemo.FK=PrimaryTableDemo.FK;
The following is the output −
+------+------+---------+ | Id | Name | Address | +------+------+---------+ | 1 | John | US | | 2 | Bob | UK | +------+------+---------+ 2 rows in set (0.00 sec)
Updated on: 25-Jun-2020
536 Views
Advertisements