The MYSQL joins are explained below with examples

Inner Join( Equi-Join)
In the inner-join the  columns of two tables. It can be used to select certain fields from both tables and only the correct rows will be joined together.

Syntax:

SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)  

xample :

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2
WHERE (
test1.name = test2.name

)

Id	name	id	favcolor
1	sree	1	black
2	divs	2	white

Cross- Join

Cross-join of two tables takes data of each row in table1 and joins it to the data from each row in table2.

Syntax: SELECT <column_names> FROM <table1>, <table2>

Example :

test1

Id	Name
1	Sree
2	divs
3	dev

test2

Id	favcolor	Name
1	black	Sree
2	white	divs

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2

id	name	id	favcolor
1	sree	1	black
2	divs	1	black
3	dev	1	black
1	sree	2	white
2	divs	2	white
3	dev	2	white

Left Join &Right join

Left Join

according to the match condition.It will show all values from left table whether there is amatching value in right or not

Syntax:

SELECT <column_name> FROM <Table1> [join|LEFT JOIN |RIGHT JOIN ] <Table2> on  (Table1.column = Table2.column)  

for example consider the following two tables

testtable1

eid	eName	eBasicpay
1	sree	200000
2	divs	500000
3	vid	000000

testtable2

id	eid	phone
1	1	987654
2	3	322352362
3	5	1222222

example :

SQL query:

normal join will show a result like this

select eName , eBasicpay, phone

from testtable1     join testtable2   on testtable1.eid  =  testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
vid	1000000	322352362

The result of left join is    SELECT eName, eBasicpay, phone
FROM testtable1
LEFT JOIN testtable2 ON testtable1.eid = testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
divs	500000	NULL
vid	1000000	322352362

Right join

according to the match condition.It will show all values from right table whether there is amatching value in left or not

example :

SQL query:

SELECT eName, eBasicpay, phone
FROM testtable1
RIGHT JOIN testtable2 ON testtable1.eid = testtable2.eid     

eName	eBasicpay	phone
sree	200000	987654
vid	500000	322352362
NULL	NULL	1222222

You can use 'USING' clause on the Left /Right Join , if the columns that are carrying out the join on have the same name.

Syntax:

SELECT <column_name>  FROM <Table1>  LEFT JOIN <Table2>  USING (<column_name>) example :

SQL query:

SELECT *
FROM testtsable1
LEFT JOIN testtsable2 using(eid)

eid	eName	eBasicpay	id	eid	phone
1	Sree	200000	1	1	987654
2	div	500000	NULL	NULL	NULL
3	vid	500000	2	3	322352362

Joining three tables   for example take the following table as the third table   testtable3

id	eid	favcolor
1	1	black
2	2	blue
3	5	green

SQL Query
SELECT *
FROM testtsable1
LEFT JOIN testtsable2 ON testtsable1.eid = testtsable2.eid
LEFT JOIN testtsable3 ON testtsable1.eid = testtsable3.eid 

eid	eName	eBasicpay	id	eid	phone	id	eid	favcolor
1	Sree	200000	1	1	987654	1	1	black
2	div	500000	NULL	NULL	NULL	2	2	blue
3	vid	500000	2	3	322352362	NULL	NULL	NULL

Good Luck :)

* create_function :

This function will create a php function .This function returns a unique function name as a string, or FALSE on error on creation.

Syntax :create_function( string $argument_list , string $function_code )

for example
<?php
$addnumbers = create_function('$no1,$no1', 'return "sum = " . $no1+$no2;');
echo $addnumbers(12,100) . "\n";
?>

output

sum=112


* function_exists

This function can be used to check whether a function is defined or not .It teturn true if the function has been defined

for example


<?php

function myfunction(){
echo "Reached here";
}

$fun='myfunction';
echo function_exists($fun)?" function $fun exist <br>":" $fun doesn't exist <br>";

$fun='myfunction11';
echo function_exists($fun)?" function $fun exist <br>":" $fun doesn't exist <br>";
?>

output :
function myfunction exist
function myfunction11 doesn't exist


* is_callable()

this function checks whether a variable can be called as a function or not


* call_user_func

This function call a user function

syntax: call_user_func ( function ,parameter)


for example


<?php
function egfunction(&$age){
echo $name."\n";
$age++;
}
$age=22;
echo "my age before function call ".$age;
call_user_func('egfunction', $age);
echo "my age after function call ".$age;
?>

output :

my age before function call 22
my age after function call 23


* call_user_func_array

this function call a function with an array of parameters.
for example


<?php

function myfunction($no1, $no2) {
return $no1+ $no2;
};

$func='myfunction';
echo call_user_func_array($func, array(300,500));

?>

output

800


* func_num_args :

This function returns the number of arguments passed to the function,
This function can be used with func_get_arg() and func_get_args() to ensure that the right number of arguments have been passed to a function.

for example


<?php
function sampleFn()
{
$argNo = func_num_args();
echo "Number of arguments: $argNo \n";
}

sampleFn();
sampleFn('only one arg');
sampleFn('two', 'args');
sampleFn(1,2,3,4,5,6,7,8,9,10);
?>

output :

Number of arguments : 0
Number of arguments : 1
Number of arguments : 2
Number of arguments : 10

Note : in javascript we can use arguments.length to find the length
alert(arguments.length);


* func_get_arg

This function returns an item from the argument list.

syntax : func_get_arg ( $num )


<?php
function myfunction()
{
$No = func_num_args();
echo "Number of arguments: $No<br />\n";
if ($No >= 2) {
echo "Third argument is: " . func_get_arg(2) . "<br />\n";
}
}

myfunction ('v1','V2','V3','V4');
?>

output :
Third argument is V3


* func_get_arg

<?php
function myfunction()
{
$no = func_num_args();
echo "Number of arguments: $numargs<br />\n";
$args = func_get_args();
for ($i = 0; $i < $no; $i++) {
echo "Argument". $i+1." is: " . $args[$i] . "<br />\n";
}
}

myfunction ('v1','V2','V3','V4');
?>


output

Argument 1 is V1
Argument 2 is V2
Argument 3 is V3
Argument 4 is V4

UNION can be used to combine two or more result sets from select statements into a single result set.

Syntax :

<SELECT statement1> UNION [DISTINCT | ALL] <SELECT statement2> UNION [DISTINCT | ALL]


for example consider the following tables


SELECT * FROM table1;


----------------------------------------------
| name1 | name2 | add1 |
--------------------------------------------
| Princy | Peter | R/220 V st |
--------------------------------------------
| Joban | John | R/456 Mst |
--------------------------------------------


SELECT * FROM table2;
------------------------------------
|Company | Address |
-------------------------------------
|TCS | 24/cChennai|
-------------------------------------
|Cubet | 43C EKM |
------------------------------------


SELECT * FROM table3;

-------------------------------------------------
|studname1|studname2|address |
-------------------------------------------------
| aarathi | Sharma | 122c/Nst|
--------------------------------------------------
| Sagar | C | 143c/Nst|
------------------------------------------------
|Sree | Nithya | 132c Dst |
-------------------------------------------------

mysql Query :

SELECT name1 , name2, addr1 FROM table1
UNION
SELECT Company, "", Address FROM table2
UNION
SELECT studname1, studname2, address FROM table3;



Result will be

---------------------------------------------------
| name1 | name2 | add1 |
----------------------------------------------------
| Princy | Peter | R/220 V st |
----------------------------------------------------
| Joban | John | R/456 Mst |
----------------------------------------------------
| TCS | | 24/cChennai |
-----------------------------------------------------
|Cubet | | 43C EKM |
-----------------------------------------------------


Union all is the default in UNION and Distinct can be used to avoid the duplication in selection



Good Luck :)

Pattern Matching in mysql

My sql provides two functions LIKE and NOTLIKE for Simple pattern matching

LIKE

Syntaxt for using like is expr

LIKE <pattern >

The Like provides '%' and _ as patterns

For example consider following table

sampletable
---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------
| Nitha | sree |
------------------------------

different types of patters using like


SELECT * FROM sampletable WHERE fname LIKE 'J%';

This will select first and 3rd row from the above table .

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
------------------------------


SELECT * FROM sampletable WHERE fname LIKE '%J%';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------

Next we can use five '_' to get records in which fname has 5 letters
SELECT * FROM sampletable WHERE fname LIKE '_____';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
| Nitha | sree |
------------------------------

REGEXP :

It matches a pattern with regular expression

Following is the pattern which can be used along with REGEXP

* a '.' can be used for any single character.

* s set of character in "[...]" matches any character within the brackets.

* "*" matches zero or more instances of the thing preceding .

* can use "^" at the beginning or "$" at the end of the pattern.

* + 1 or more

* ? 0 or 1

* {n} exactly n

* {n,} n or more

* {n,m} between n and m

* | either, or

* [^...] Any character not listed between the square brackets


some sample queries are shown below.

* for the names containing a "j" we can use the query
SELECT * FROM sampletable WHERE fname REGEXP 'j';

* for the names beginning with J we can write a query as follows
SELECT * FROM sampletable WHERE fname REGEXP '^J';

* for the nameending with 'a' we can write a query like
SELECT * FROM sampletable WHERE fname REGEXP 'a$'

* for the name with four characters we can use the query
SELECT * FROM sampletable WHERE fname REGEXP '^....$';
(or)
SELECT * FROM sampletable WHERE fname REGEXP '^.{4}$'

* Query to find all the names starting with a vowel and ending with 'y'
SELECT * FROM sampletable WHERE fname REGEXP '^[aeiou]|y$';





Enjoy :)

MySQL INDEX

A database index is a data structure that improves the speed of operations in a table.Indexes are also can be considered as a type of tables which keeps primary key or index field and a pointer to each record in to the actual table.The users cannot see or use the indexes defined on tables , they are used by Database Search Engine to locate records very fast.

When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.

* Allow the server to retrieve requested data, in as few I/O operations
* Improve performance
* To find records quickly in the database

for example


CREATE TABLE sampletable (id INT, fname VARCHAR(50), lname VARCHAR(50), INDEX (id))

Simple and Unique Index:
in this type of indexing two rows cannot have the same index value

syntax :CREATE UNIQUE INDEX ON ( column1, column2,...);

CREATE UNIQUE INDEX F_INDEXON tab1 (tab2)


Points to consider for optimizing the MySQL Indexes

* The columns with the most unique and variety of values should be used.
* Smaller the index better the response time.
* For functions that need to be executed frequently, large indexes should be used.
* Avoid use of index for small tables.


Good Luck :)