How to use direct connection in MySQL?

Direct joins in MySQL work similarly to inner joins or joins. This means it only returns Matching lines. First, we need to understand Straight join in MySQL. For this we need Create two tables and associate them with foreign key constraints.

This is the first table

mysql> CREATE table ForeignTableDemo
-> (
-> Id int,
-> Name varchar(100),
-> FK int
-> );
Query OK, 0 rows affected (0.47 sec)

This 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 the 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 the records will be Add to 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)

Display 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 the records to 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)

Display all 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 direct connection is as follows, only matching rows are displayed-

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)

The above is the detailed content of How to use direct connection in MySQL?. For more information, please follow other related articles on the PHP Chinese website!