Difference between INNER and OUTER joins


primary vs  candidate vs composite key unique.

one table one primary key they value not null if you make other primary key in same table this is composite key this is allow null and blank value.

primary key is candidate key if you make one table more primary key

unique is one table more then one this value is null accept one time

PK can easily be ‘auto incremented’ by the database.This doesn’t hold for a composite key.


php oops question answer


Assuming you’re joining on columns with no duplicates, which is a very common case:

  • An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
  • An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.


Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a |  b
1 | null
2 | null
3 |    3
4 |    4

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5


Question: How to make duplicate table in MySQL?

CREATE TABLE users_new LIKE users; //Copy the structure
INSERT users_new SELECT * FROM users; //copy the table rows

Question: How to find duplicate email records in MySQL?

SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
    SELECT email FROM users GROUP BY email HAVING count(email) > 1
    ) u2 ON u1.email = u2.email;

Question: Difference between utf8_general_ci and utf8_unicode_ci?
utf8_unicode_ci is more accurately as compare to utf8_general_ci.
utf8_unicode_ci is support more lanaguages as compare to utf8_general_ci.
utf8_unicode_ci is faster at comparisons and sorting as compare to utf8_general_ci.

Question: How to get 2nd highest salary of employee, if two employee may have same salary?

select salary from employee group by salary order by salary limit 1,1