Main Purpose of FOREIGN KEY When I did my first interview, I faced this confusing question.
By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql?

Please suggest me the best answer.
The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI). Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others).

This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.

As explained in the Wiki, RI is 


  a property of data which, when satisfied, requires every value of one
  attribute (column) of a relation (table) to exist as a value of
  another attribute (column) in a different (or the same) relation
  (table).


To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:

CREATE TABLE order_header
(
  order_id INTEGER,
  client_id INTEGER,
  order_date DATE,
  &lt;...other fields depending on your individual requirements...&gt;
);


You might then have a client table like this

CREATE TABLE client
(
  client_id INTEGER,
  client_name VARCHAR(50),
  client_address VARCHAR(50),
  &lt;.... other fields depending on your requirements...&gt;
);


You would then add a CONSTRAINT to your order_header table with something like this:

ALTER TABLE order_header
    ADD CONSTRAINT fk_header_client 
    FOREIGN KEY (client_id) 
    REFERENCES client (client_id);  
-- the exact syntax may differ per RDBMS.


This is telling the RDBMS that on the client_id column of the order_header table, there is now a constraint such that any client_id in order_header must already exist in the client table.

The referenced FOREIGN KEY columns(s) on the parent table need to have a UNIQUE constraint (or be the PRIMARY KEY). 

Otherwise, it wouldn't make much sense - if there were 2 clients with the same client_id, how would the system be able to tell them apart - what address do you send the order to? 

Note also that with many systems, you can create the CONSTRAINT in the CREATE TABLE statement.

Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.

Many optimizers can also take advantage of some optimizations when there are FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.

Finally, a further advantage of using FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create INDEXes on the FOREIGN KEY columns.