SQL Inner, Left, Right and Full Joins
Today, We want to share with you SQL Inner, Left, Right and Full Joins.
In this post we will show you SQL query to join two or more tables Example, hear for SQL | Join (Inner, Left, Right and Full Joins) we will give you demo and example for implement.
In this post, we will learn about SQL INNER JOIN – Joining Two or More Tables with an example.
SQL joins query is used to one or more combine rows two or more tables.
The SQL INNER JOIN is a Simple join.There are Different types of JOINs.
First : INNER JOIN:
Fetch all rows when there is at least one match recod data in BOTH tables.
Second : LEFT JOIN:
Fetch all rows from the left table Only, and the matched rows from the right table only.
Third one :RIGHT JOIN:
Fetch all rows from the right table Only, and the matched rows from the left table Only
Fourth : FULL JOIN:
Fetch all rows when there is a match in ONE of the tables(Both tables)
Easy way to SQL INNER JOIN Example:
SQL Simple INNER JOIN Example:
SELECT technology.technologyName, Projectportal.ProjectportalID FROM technology INNER JOIN Projectportal ON technology.technologyID=Projectportal.technologyID ORDER BY technology.technologyName;
Second way SQL INNER JOIN Example:
SELECT adverts.advertsid,adverts.advertmaintypeid,adverts.shortdesc,adverts.packagenm,adverts.advertnm,adverts.adverturl,adverts.adverttypeid, adverts.advertlogo, adverts.advertdesc,adverts.apstatus,adverts.advertrate, adverts.gender,adverts.selectcity,adverts.sequence, adverttypes.adverttypeid,adverttypes.adverttypenm,adverts.commissiontype FROM adverts INNER JOIN adverttypes ON adverts.adverttypeid=adverttypes.adverttypeid and adverts.status=0 and adverts.apstatus=1 and adverts.gender IN($mgtype,0) and (adverts.selectcity LIKE '%$cityselected%' or adverts.selectcity=0) ORDER BY adverts.sequence ASC
SQL UNION with LEFT OUTER JOIN Example:
(SELECT device_token FROM usermst WHERE userid = $userid AND device_token != '') UNION (SELECT usermst.device_token FROM usermst LEFT OUTER JOIN usergroupmap ug ON ug.userid = usermst.userid WHERE ug.groupid = $gid AND usermst.device_token != '')
SQL SELECT DISTINCT Statement (at a time uniq) Example:
SELECT DISTINCT device_token FROM usermst where trim(device_token) != '' and userid='$userid' <h3>SQL USING LEFT OUTER JOIN Example:</h3> select * from usertransactions ut left outer join adverts ad ON ad.advertsid=ut.advertsid where ut.userid = $userid and ut.status =1 and amount!='0' and exists(select advertsid from adverts where advertsid=ut.advertsid ) ORDER BY ut.usertransid DESC
SQL using sum in where clause Example:
SELECT SUM(amount)-$amount as amount FROM usertransactions ut WHERE userid=$userid and status=1 and crdr=1 and exists(select advertsid from adverts where advertsid=ut.advertsid)
SQL query alias column Example:
SELECT u.userid,u.usernm,ut.crdr,a.advertnm,a.advertlogo FROM usermst u INNER JOIN usertransactions ut ON u.userid = ut.userid INNER JOIN adverts a ON a.advertsid = ut.advertsid where ut.usertransid=$usertransid
We hope you get an idea about SQL Inner, Left, Right and Full Joins
We would like to have feedback on my Information blog .
Your valuable any feedback, Good question, Inspirational Quotes, or Motivational comments about this article are always welcome.
If you liked this post, Please don’t forget to share this as Well as Like FaceBook Page.
We hope This Post can help you…….Good Luck!.