Posted inProgramming / Mysql / Mysqli / php

SQL Inner, Left, Right and Full Joins

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

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'

SQL USING LEFT OUTER JOIN Example:

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!.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype