Sometimes if we have to write some joins sql query in magento we cand write those joins as :

public function joins()
{
        $table = $this->getMainTable();
        $table2 = $this->getTable('customer/entity');
        $cond = $this->_getReadAdapter()->quoteInto('t1.id = t2.customer_id','');
        $where = $this->_getReadAdapter()->quoteInto('t1.list_id = "?"',123);
        $select = $this->_getReadAdapter()->select()->from(array('t1'=>$table))->join(array('t2'=>$table2), $cond)->where($where);
        echo $select."<br/>";
        $select = $this->_getReadAdapter()->select()->from(array('t1'=>$table))->joinLeft(array('t2'=>$table2), $cond)->where($where);
        echo $select."<br/>";
        $select = $this->_getReadAdapter()->select()->from(array('t1'=>$table))->joinRight(array('t2'=>$table2), $cond)->where($where);
        echo $select."<br/>";
        echo $select."<br/>";

if we notice in the above quarries then we will find that some functions are used like join(), joinLeft(), joinRight etc,

The above query is seen as :

SELECT `t1`.*, `t2`.* FROM `tablename` AS `t1` INNER JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")
SELECT `t1`.*, `t2`.* FROM `tablename` AS `t1` LEFT JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")
SELECT `t1`.*, `t2`.* FROM `tablename` AS `t1` RIGHT JOIN `customer_entity` AS `t2` ON t1.id = t2.customer_id WHERE (t1.list_id = "123")