Usage difference between list and left join

Question

I have two database tables with the following structure:

actions:

action_id   int(11) primary key
action_name     varchar(255)
action_module   varchar(45)

permissions:

perm_id     int(11) primary key
perm_role   int(11) 
perm_action     int(11)
perm_status     int(11)

Now I need to check if there is an entry in the permissions table for this role in the permissions table by entering the following data: perm_role, action_name and action_module.

I have prepared two queries to check the above condition, but I have no clue which one is better. Can someone help me find the best option:

Request 1

SELECT perm_id FROM permissions 
LEFT JOIN actions ON action_id=perm_action 
WHERE perm_role=1 AND action_name='add' AND action_module='employee';

Request 2:

SELECT perm_id FROM permissions, actions 
WHERE perm_role=1 AND perm_action=action_id AND action_name='add' 
AND action_module='employee';

I need to optimize these requests, as this should be done during every request made to the server. Development Environment - PHP-5.2.10 and MySQL-5.1.35

Answer 1

- .

- , JOIN, . , .

MySQL - CROSS JOIN, MySQL INNER JOIN, , , CROSS JOIN INNER JOIN "" SQL, , , , .

( ) ( ). ​​ , SQL-, ! (.. WHERE) , , , ( ). LEFT JOIN, (, "" - , "" ).

, , JOIN ( , MySQL v5.x). , 3 , , , , , , . . .

JOIN MySQL - MySQL, , , :

12.2.8.1. JOIN (MySql v5)

12.2.9.1. JOIN (MySql v6)

:

INNER JOIN () : (.. ).

, INNER JOIN, CROSS JOIN, LEFT JOIN .. , , "col_name" "on".

-

, NATURAL USING, . , t1 (a, b), t2 (c, b) t3 (a, c), : t1 (1,2), t2 (10,2) t3 ( 7,10). , NATURAL JOIN :

SELECT... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

t2, (t1 NATURAL JOIN t2). t3 t2, , t3 t1, . , equi-join:

SELECT... FROM t1, t2, t3  WHERE t1.b = t2.b t2.c = t3.c;

equi-join (t1.a = t3.a). , . :

SELECT... FROM t1, t2, t3   t1.b = t2.b t2.c = t3.c t1.a = t3.a;

MySQL, , equi-join.

-

(,) JOIN , t1, t2 JOIN t3 ((t1, t2) JOIN t3). JOIN , (t1, (t2 JOIN t3)). , ON, , , .

:

CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 (1,1); INSERT INTO t2 (1,1); 3 (1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

SELECT - t1, t2 as (t1, t2). JOIN , ON - t2 t3. t1.i1 , "t1.i1" "on clause". , , ON (t1, t2) t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

, JOIN:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

, INNER JOIN, CROSS JOIN, LEFT JOIN RIGHT JOIN, , .

Answer 2

1 , join, sql , , . pu , , "=" . .

: action actionid

Answer 3

: . , . : .

: , INNER JOIN.